Thanks for the question, Ken.

Asked: June 16, 2000 - 2:33 pm UTC

Last updated: January 15, 2013 - 10:21 am UTC

Version: Oracle 8.0

Viewed 100K+ times! This question is

You Asked

Hi Tom,

A trivia question for you, what is SID and what is it used for? Would changing it have any impact on the different instances of the database? And lastly, where can I find out the SID of my database? I don't seem to able to find it in technet.oracle.com.

Please explain.

Thanks
GoJo.

and Tom said...

The SID is a site identifier. It plus the Oracle_home are hashed together in Unix to create a unique key name for attaching an SGA. If your Oracle_sid or Oracle_home is not set correctly, you'll get "oracle not available" since we cannot attach to a shared memory segment that is identified by magic key. On NT, we don't use shared memory but the SID is still important. We can have more then 1 database on the same oracle home so we need a way to id them.

Changing it harder then it looks. I know you are on unix, so here are the steps for changing it (or the database name) under Unix - they are different on NT.

How to find the sid -- "select instance from v$thread" will do that.

PURPOSE
This entry describes how to find and change the "db_name" for a database, or the ORACLE_SID for an instance, without recreating the database.

SCOPE & APPLICATION
For DBAs requiring to either find or change the db_name or ORACLE_SID.

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:

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

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_files parameter does not point to old file names, if you have renamed them.
o "crdb<sid>.sql" & "crdb2<sid>.sql", This is optional. These are only used at database creation.

5. Change locations to the "rdbms/admin" directory

% cd $ORACLE_HOME/rdbms/admin

and rename the file:

o startup<sid>.sql. This is optional. On some platforms, this file may be in the "$ORACLE_HOME/rdbms/install" directory. Make sure that the contents of this file do not reference old init<SID>.ora files that have been renamed. This file simplifies the "startup exclusive" process to start your database.

6. To rename the database files and redo log files, you would follow the instructions in <Note:9560.1>.

7. Change the ORACLE_SID environment variable to the new value.

8. 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 9. 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>

9. Start up the database and verify that it works. Once you have done this, shutdown the database and take a final backup of all control, redo, and data files.

10. 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

% svrmgrl
SVRMGR> connect internal

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 your 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.

6. Get the "CREATE CONTROLFILE" command from 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" NORESETLOGS ...
TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ...

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

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" .

11. Login to Server Manager

% svrmgrl
SVRMGR> connect internal

12. Run the "ccf.sql" script

SVRMGR> @ccf

This will issue a startup nomount, and then recreate the controlfile.

If, at this point, you receive the error that a file needs media recovery, the database was not shutdown normally as specified in step 4. You can try recovering the database using the redo in the current logfile, by issuing:

SVRMGRL> recover database using backup controlfile;

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. 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 apply the necessary redo, you need to 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.

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

The last command in ccf.sql should be:

SVRMGR> alter database open resetlogs;

13. You may also need to change the global database name:

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.

Rating

(58 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ganesh, June 17, 2001 - 9:25 am UTC

GREAT ANSWER

oracle dba

mian mazhar iqbal, September 05, 2001 - 4:20 am UTC

i found this help a great asset.

how many DBAs oracle needs to change a light bulb

Mikito Harakiri, September 13, 2001 - 1:10 pm UTC

It is amaizing how much mechanical work is required for simple things in oracle.

Relational Model is about normalisation; or to put it simply, no redundant data. What we see here: an SID in dosen of places?

September 13, 2001 - 1:18 pm UTC

erh???

The simple thing here is -- don't change the SID.

The database is a relational model -- the software that runs it, well, its not.

ACTUALLY, what we see here is someone trying to UPDATE A PRIMARY KEY.

This is actually a GREAT example of a relational model. The primary key of the database is the SID. You are trying to update the primary key (a BIG NO NO in relational databases) and have to do the "on update cascade".

So, if you use even a SINGLE foreign key -- you have the same issue. If you ever update your primary key value -- you have to do the same thing (find all of the foreign keys and update them as well).

why you would consider renaming a SID to be a "simple thing" ( i consider something very obscure -- never having actually ever having to do it myself in 15 years )......

How long should this take

frank, September 14, 2001 - 3:12 am UTC

I tried something similar to copy a 70G db. (Didn't want to exp-imp).
I copied all dbf-files & redologs, then copied & altered init.ora
Now when i run my "ccf" file, it just seems to hang. I don't see nothing happening, no ctl-files appearing, no CPU (in top) no reaction to ^C
How long should this process of (re)creating ctlfiles take?

September 14, 2001 - 10:30 am UTC

it should be virtually instantaneous -- seconds at most.

Great answer

Joel, September 14, 2001 - 11:00 am UTC

Thanks, Tom....this is something that has always puzzled me.

dba

lucas mashele, November 15, 2001 - 3:00 am UTC

highly informative

SID

Ofoe, February 16, 2002 - 9:52 am UTC

Tom ,
Can you an example in changing the SID on WINDOWS 95/98 platforms?

February 22, 2002 - 9:32 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:634671569269 <code>

shows that.

an, February 22, 2002 - 10:20 am UTC

great informative!

How did you connect in the first place?

Liz, March 01, 2002 - 1:07 pm UTC

Excellent description of changing a sid. I did that all the time at a previous job to create test instances. I found the why a sid portion to fascinating. It was how to find out a sid portion that got me wondering. If you don't know the sid, how did you connect to the instance to ask it what it's sid is?

March 01, 2002 - 1:31 pm UTC

ps -<whatever flags are appropriate on your system> | grep pmon

for example:

$ ps -aux | grep pmon
ora734 3662 0.0 0.518720 6896 ? S Jan 21 0:00 ora_pmon_ora734
tkyte 7446 0.0 0.1 944 784 pts/8 S 13:30:09 0:00 grep pmon
ora716 12929 0.0 0.417472 5552 ? S Jan 22 0:00 ora_pmon_ora716
oracle9i 13112 0.0 13.6249688205872 ? S Jan 18 0:00 ora_pmon_ora9iUTF
ora806 13901 0.0 1.03324014080 ? S Feb 26 0:00 ora_pmon_ora806
oracle9i 17941 0.0 24.6413528372528 ? S Feb 18 0:00 ora_pmon_ora9i
ora817 25122 0.0 16.2279824245824 ? S Feb 01 0:00 ora_pmon_ora817dev
ora815 25424 0.0 2.99008043712 ? S Jan 28 0:00 ora_pmon_ora815

shows 7 sids I currently have running on my testbed machine -- ora_pmon_$ORACLE_SID

If you aren't on the server -- the sid just doesn't matter, you need the tns entry found in the tnsnames.ora file.

On NT, look at the list of Oracle services in control panel.

soooo cool!

Fenng, August 20, 2002 - 3:03 am UTC

It's so cool.
But don't know if it's fit for Oracle9i.
thanks a lot !

August 20, 2002 - 7:45 am UTC

c/svrmgrl/sqlplus/

Thanks Mr. Tom

Arun Kumar K T, October 06, 2002 - 11:51 am UTC

Realy it is fantastic.

how to recreate the controlfile in Oracle9i

Yakgna, March 20, 2003 - 1:53 am UTC

tom,

can you please explain me what all steps needed to recreate control file with/without existing control file in oracle 9i.
Do we need to backup SPFILE and PFILE while doing so?

as always, thanks for everything you do

Yakgna

March 20, 2003 - 8:50 am UTC

You have no existing control files -- no binary backups, no "backup to trace"?

Then the only tool you need is vi or your favorite text editor. You input a CREATE CONTROLFILE statement with all of the locations of all of your files.

I don't understand "backing up the spfile or pfile" in this context though? No, you don't need to back them up -- they are what tell Oracle where to find the controlfiles - where to put them when you do the create controlfile.

when and how is the DBID generated?

jianhui, June 19, 2003 - 3:29 pm UTC

Tom,
When i use clean cold backup to do the database cloning, i found that no matter i use
1) create control file REUSE database
or
2) create control file SET database
the DBID is alway the same as original source database.

So could you explain the question in the title?

Secondly, what is the initial parameter instance_name used for? I can set it to any value and still be able to bring up the database even the the instance SID is totally different. For example, the instance SID is A, i can set instance_name to B or A, they both work. Is it used for OPS? I need a clue.

June 20, 2003 - 3:54 pm UTC

dbids are burned into datafiles when the database is created.

you can change it with $ORACLE_HOME/bin/nid (db new id utility) in 9i

check out the Oracle REFERENCE guide for all details on all parameters, but basically this is a setting you really don't need to touch, especially if you are "single instance Oracle" (no OPS, no RAC). In a cluster, if all of the instances of Oracle running had the same sid, you might want to name each instance differently so you could tell which one you were connected to (or to make connecting to a specific instance possible)

Cloning an Open database

kumar, June 30, 2003 - 8:35 am UTC

Tom,

Can you please validate the following procedure to do the cloning of a open database ?

1. Take a hot backup of all the datafiles
2. Transfer the datafiles and archive logs to the new server.
3. Get the control file to trace. Edit the parameters for as for normal cloning.
4. In the new init.ora file change the archive destination
and all other relavant settings as for normal cloning.
5. run create controlfile
6. recover database using backup controlfile until cancel
7. open resetlogs

Is there any other way to do this (excluding export/import) ?

Thanks

June 30, 2003 - 9:20 am UTC

rman does it with a command.

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmdupdb.htm#441628


doing it manually like you propose is the same as an "incomplete media recovery" and is outlined here:

http://docs.oracle.com/docs/cd/B10501_01/server.920/a96572/performingreco.htm#14614 <code>

Cloning across OS

Ma$e, August 13, 2003 - 11:10 am UTC

Hi Tom:

Look forward to your sessions at the Oracle World-2003.

On the subject of this discusion:
Can a d/b be cloned across OS ? Say from Solaris to NT/2K ?

Thanks.

August 13, 2003 - 11:29 am UTC

not in 9iR2 and before... :) read into that "and before" what you will

May be in 10G

Ma$e, August 13, 2003 - 1:36 pm UTC

Do I read then 10G will support it ?

August 13, 2003 - 2:11 pm UTC

you read what you want ;) ;)

wink wink

I'm feeling tidly winky now....

Ma$e, August 13, 2003 - 3:10 pm UTC

Hi Tom:

On the serious side of things...

What are the dependancies for cloning a d/b across the same o/s. Issues like:

1) Patch sets applied on the source and target servers
2) Versions of Oracle and OS Binaries
3) any other issues

Thanks

Ma$e

August 13, 2003 - 3:14 pm UTC

come back in a month or two -- sorry.

I meant for cloning on 9i Rel 2 or earlier versions.

A reader, August 13, 2003 - 3:15 pm UTC

August 13, 2003 - 3:20 pm UTC

ahh - sorry, clones should be clones

same os rev
same oracle rev
same patch levels
"clone"

Thanks...

A reader, August 13, 2003 - 4:12 pm UTC

what happens step by step when alter database open resetlogs

yohann, October 15, 2003 - 12:04 pm UTC

Hi Tom,
I try to understand that:
I create a clone database; but at the time to restore datafiles, one or more datafiles are read-only (r--r--r-- on UNIX); this a bug of the backup software.

So when I "ALTER DATABASE OPEN RESETLOGS", I have:
Ora-1190, 00000, "controlfile or data file %s is from before the last RESETLOGS"
Up to here, I understand why (i "understood" is the exact term...)
Now when i issue a RECOVER DATABASE, all is OK and I can OPEN DATABASE! here I don't understand anymore !!!

What happens precisely inside Oracle? What checks are made, in which files (control files, datafiles)?
I tried to dump files at each step, but can't understand.

Thanks for your help.

October 15, 2003 - 6:36 pm UTC

why did you think the rrr has something to do with this?

does the rrr become writeable at some point?

if you change the files from rrr to writeable first -- does it still happen

why are you associating the two things?

are the files in the backup actually from before the last resetlogs.

what is the entire scenario here

yohann, October 16, 2003 - 9:52 am UTC

Oh... sorry. I'll try to be more precise and to not forget any crucial information:

here are the steps:
1. restore a COLD backup (shut down normal).
The file users01.dbf is r--r--r-- (instead of rw-r--r--)

2. CREATE CONTROLFILE SET DATABASE <SID> RESETLOGS ARCHIVELOG ....
=> OK: Control files are created

3. ALTER DATABASE OPEN RESETLOGS;

see the alert_SID.log contents:
ORA-01110: data file 5: '/oracle/SID/oradata/SID/users01.dbf'
ORA-01114: IO error writing block to file 5 (block # 1)
ORA-27070: skgfdisp: async read/write failed
SVR4 Error: 9: Bad file number
Additional information: 1
Thu Oct 16 12:27:06 2003
ORA-1190 signalled during: alter database open resetlogs

Note : REDO LOGS are now created, and CTLF re-initialized

4. I took a dump of datafile headers and a dump of control file.
when analyzing the dump of datafile headers, i see that the "reset logs count", "chkpt cnt" and "ctl cnt", doesn't match between datafiles(since users01.dbf was read-only on UNIX, Oracle couldn't make any change in the file) and between the content of controlfile.
moreover the "rba" field has not been reset in the file header.(is it the same as what is called the Log Sequence Number?)
But the controlfile has been written with the informations of the RESETLOGS (new SCN, sequence number reset to 1, and so on...)

5. chmod 640 users01.dbf (that's the crucial info i've omitted)
6. RECOVER DATABASE
Since resetlog count, checkpoint count, ctl count,etc... doesn't match, why Oracle does accept to RECOVER a file that is from a previous incarnation??? what is the algorithm to check CTL files and datafiles to decide to recover or not?

7. ALTER DATABASE OPEN
=> OK : database open ; all counters are then OK in the headers.

Thank you very much for your help.

October 16, 2003 - 10:54 am UTC

6 -- it'll do that in the event there is sufficient data in the available logs to do so. since NO information was actually needed, it worked by accident.

there are cases when you can recover through a resetlogs -- it can be tricky (never never rely on it 100% -- you need to be in a special state). Rman can do it as well -- might want to read support notes <Note:1070453.6> <Note:237232.1>

you actually didn't need to apply any redo, you got lucky.

But - you now know (via testing) what your restore procedure SHOULD be -- add the chmod after the restore and you'll never be in this situation. Good thing you are practicing.

Good

manoj, May 19, 2004 - 12:17 am UTC

Tom, As usual very good information.

Just curious to know ..can we use below statement to get instance name ?

ret_val := dbms_utility.get_parameter_value('instance_name', intval,l_sid );

May 19, 2004 - 9:44 am UTC

ops$tkyte@ORA9IR2> declare
  2  l_string varchar2(255);
  3  l_dummy  number;
  4  begin
  5     l_dummy := dbms_utility.get_parameter_value( 'instance_name', l_dummy, l_string );
  6     dbms_output.put_line( l_string );
  7  end;
  8  /
ora9ir2
 
PL/SQL procedure successfully completed.

INSTANCE_NAME on RAC

Jan, May 20, 2004 - 5:53 am UTC

On Linux, Oracle 9i, I tired to install RAC on Single Node with 2 instances. I have following scenario:

ORACLE_SID=db9i

Database is using spfile. In an init.ora file ($ORACLE_HOME/dbs/i1.ora), I have:

---------
instance_number=1
thread=1
instance_name=rac1_i1  
undo_tablespace=undotbs1
spfile=/dew/raw/raw57
---------

Please notice instance_name.

Now, I run SQL*

> sqlplus 'sys/sys as sysdba'
SQL> startup pfile=$ORACLE_HOME/dbs/i1.ora

database started

SQL> show parameters instance_name

name              ... value
----------------      --------
INSTANCE_NAME         db9i1     --???

SELECT instance_name FROM v$instance;

INSTANCE_NAME
-------------
db9i  


Questions:

1) Why is an INSTANCE_NAME in init.ora ignored?
2) How does it come with instance_name DB9i1
3) Why I have 2 different instance_name?

My main goal is to name instances as I want to, and then reference these instance names in tnsnames.ora, but the name given init.ora but is ignored.

Thank you, Jan

May 20, 2004 - 11:21 am UTC

....to install RAC on Single Node with 2 instances.....

hmm, well, while there are super special notes for doing such a thing (you can get them from support if you like), it is not really practical, realistic or very useful.

I cannot reproduce, when I do an alter system set instance_name=foobar scope=spfile and reboot -- it does it.

I would have two oracle_sids here and use spfiles for each (easier).

if $ORACLE_SID <> INSTANCE_NAME ...

Jan, May 20, 2004 - 11:48 am UTC

Twoo Instances on single node - it is just for testing RAC.
I found the error - the instance_name in pfile was overriden by *.instance_name in spfile (I forgot to unset it).

But there is still a question, why if I have $ORACLE_SID different from INSTANCE_NAME, the value from V$INSTANCE is not consistent with value from V$PARAMETER.

May 20, 2004 - 12:20 pm UTC

instance_name in v$instance is the oracle_sid, they should always be set to the same. if you just set the oracle_sid and skip the instance_name init.ora parameter, it'll be "so" for you out of the box.

Cloning using RMAN

dharma, July 09, 2004 - 2:12 pm UTC

Hi Tom,

Excellent article.If I have to clone using RMAN then both the auxiliary and target have to be reachable(connected). I have a situation where I have to create an auxiliary which is not connected to the target. Is there any way to clone using RMAN. What would be the way to go.

Thanks,
-dharma

July 09, 2004 - 3:58 pm UTC

backup target, restore to "clone"

just restore a backup.

clone need (usualy but not always) the same OS

Darek, October 19, 2004 - 5:24 am UTC

Hallo Tom, thanks for your work, its great.

About cloning with RMAN I have found such info:

"If you can make an operating system copy of a database file on one platform and then restore it to another platform, then it is also possible to duplicate a database from one platform to another. Otherwise, duplication is not possible."

</code> http://zuse.esnig.cifom.ch/database/doc_oracle/Oracle901_Linux/server.901/a90136/rcmsyn27.htm <code>

Darek

October 19, 2004 - 9:20 am UTC

until 10g, you cannot transport datafiles across OS's in any supported supportable fashion. period.

windows -> windows
windows NOT -> linux, hp, solaris, whatever

in 10g, you can transport datafiles over OS's (using rman if needed)

What is a SID, how to change it, how to find out what it is

Vicky, November 06, 2004 - 11:33 am UTC

Hi Tom,

I wanted to change SID of our test database. So I have following question regarding the prescribed script. We are using Oracle 8.1.7 on UNIX.

(1)Can I change service_name? And if yes could you please explain steps?

(2) In step number 3 for Modifying a database to run under a new ORACLE_SID I didnt get what did you mean by 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) Could you please explain me what to do here and how to do it?

(3)In the script where ever you used the word This is optional I am going to omit all those steps as I dont want to take any risk. Will I be alright if I will omit all optional steps?

(4)In the step 6 of Modifying a database to run under a new ORACLE_SID I am going to follow <Note: 9560.1>

(5)How to change ORACLE_SID environment to new value? (Sorry I am new in UNIX) and we do not have to change this in init<newSID>.ORA file?

(6)Cant I change DB_NAME = newDB_NAME while changing ORACLE_SID? I mean cant I perform both the tasks (Changing ORACLE_SID as well as DB_NAME) to gather?

Thanks Tom,

Regards,
Vicky

November 06, 2004 - 12:15 pm UTC

1) so, you want to change a SID but your first question is service_name?

service name is something you either hard coded into the listener.ora or you set as a init.ora parameter. depending on where you did it, change it there...

make sure to update all of your tnsnames.ora files as well then....

2) sort of self explanatory?? if you are on unix, the oracle_sid is an environment variable and you probably have it set in all kinds of login scripts. You need to "fix that"

3) as long as you read the points and understand what they are saying, sure.

5) depends on your shell,

$ export ORACLE_SID=whatever
$ setenv ORACLE_SID whatever

are two common techniques -- but as your UNIX friends using the same shell as you.

6) changing a sid is really just renaming and relocating a bunch of files, it isn't a "database change" in as much as a logistical nightmare of rename files so when Oracle takes the $ORACLE_SID we can find everything again.

Thanks

A reader, November 06, 2004 - 8:23 pm UTC

Thanks Toma,
The answer was really helpful.

Regards,
Vicky

Sorry for Typing Mistake Tom

A reader, November 06, 2004 - 8:24 pm UTC

Regard,
Vicky

change log source before duplicate command

A reader, November 09, 2004 - 11:58 am UTC

9.2.0.5 on HP-UX
Tom,
During cloning (Target and host on different boxes)
we copy the backups and archived logs to a host directory...
and the directory structure is not the same some times..
so we use soft links cheat to set the source path to look
for archived logs and backups to start recovery.
Is there a preferred way to do this.
Like some init parameter or can SET LOGSOURCE be used in side a RMAN run block..

Thanks.

Export-Import won't do it?

Mark, November 10, 2004 - 10:32 pm UTC

Hello Tom,
I am on UNIX using Oracle 8.1.7. I went through all discussion and I think instead of doing all these steps can't I use export-import utility to change ORACLE_SID? As I want to change ORACLE_SID of my test db which is very small. It takes hardly 5 minutes to copy all data files, log files and control files. I am planning to perform following steps please let me know am I right or missing something?

(1)Create an empty database with new SID and new db name
(2) Export everything from old database
(3) Import everything into new database created in step 1
(4)Start new database
(5) Add new oracle_sid in .profile, .cshrc, .login,
oratab, tnsnames.ora
(6) And all users are ready to access new database with new
ORACLE_SID!!!!

Thanks for your help,

Mark

November 11, 2004 - 7:54 am UTC

ORACLE_SID is *an environment variable*

ORACLE_SID is used to *find configuration files*

ORACLE_SID is not stored in the *DATABASE AT ALL*.

USERS do not access a database via an ORACLE_SID really, they use a tnsnames.ora entry which in 2004 should be a "service" which means the ORACLE_SID is nothing KNOWN outside of the server itself (and can be changed without touching a single BIT of data in the database).

export import would do it, but that is like reinstalling the server everytime you want to start it up (eg: slightly way overkill)

Thanks

A reader, November 12, 2004 - 1:25 am UTC

Thanks for your help Tom.

Regards,
Mark

Thanks

EMCotrina, May 30, 2005 - 7:14 am UTC

If you are going to do this, do not forget to change the listener.

It is wonderful to have this information, it is a good checklist when you try to do the SID Change.

I did it and it worked.

Next step pls.

ARU, July 11, 2005 - 1:15 am UTC

Hi Tom,

What I have done is as follows upto now:-

1) Installed software without installing database on server
called 'B'.
2) Taken hot backup of prod database('PROD') and copied files over to server B (with same directory structure).
3) Taken "backup controlfile to trace" of PROD db and brought it over to 'B', edited it to specify SET and new db name 'TEST' and all necessary changes as from this thread.
4) Taken the archivelogs across to 'B'.
5) Created controlfile after Startup Nomount of database.

Now before I go on -
1)what I do not understand is that how will the 'TEST' database be able to apply the archivelogs when it is of different name and has no information of the archivelogs at all.
2)Should I have kept the name of the database same as PROD and after the archivelogs have been applied (and database opened with resetlogs option), then changed it to 'TEST'.

Please help Tom,
Thanks,
Regards,
ARU.

July 11, 2005 - 8:33 am UTC

you would do a recover until cancel, it understands this is a controlfile without history, it'll generate the "guessed names" (which if you use the same locations should be correct). You'll keep apply redo until you run out at which time you cancel and "open resetlogs".

You did not need to rename the database, it is an unnecessary complication.

Changes made to rename sid

Ashish, November 17, 2005 - 5:51 pm UTC

Hi Tom,

I have made changes as suggested to rename sid from adam to dwadam and it worked fine. Then I shutdown the database in NORMAL mode and exited from the Linux prompt as oracle user.

When I again tried to login as Oracle user it diplays

No such sid "adam".
Using default values.
no VLM_WINDOW_SIZE entry for , using default (512MB)

LD_LIBRARY_PATH=/opt/app/oracle/product/10.1.0.4/A02db/lib:/opt/third-party/lib:/usr/lib
ORACLE_SID=
ORACLE_HOME=/opt/app/oracle/product/10.1.0.4/A02db
SHLIB_PATH=/opt/app/oracle/product/10.1.0.4/A02db/lib
TZ=UTC
VLM_WINDOW_SIZE=
DISABLE_MAP_LOCK - 1

When checked for $ORACLE_SID it returns nothing.

Which files under which locations should I check in to get it working.

Appreciate your help!

November 18, 2005 - 10:18 am UTC

what are you "sourcing" when you log in. perhaps you want to update your oratab if you are using a script that reads it.

another question on cloning

Ajeet, May 16, 2006 - 11:32 am UTC

Hi Tom,

Suppose I have 12 datafiles in my source database and now I want to clone it on anotehr oracle instance.

everything works fine per the process you gave here.
But I removed 1 datafile from my ccf.sql (the backup to trace of control file ) ,infact I did it knowingly as the file was very big and i had a hard time in copying it to target server.

now when I try to include it in my create control file...sql script ..it will give an error ..that is the file header refer to old database name (Sorry not in my office so could not get the exact error ,but i guees you know it all ) ..

what is a way to fix this..resolve this problem.
it can happen ..with many others i guess..

Regards
Ajeet

just to add one more detail in my above question

Ajeet, May 16, 2006 - 11:34 am UTC

Hi Tom,

The header mismatch error comes , when I try to recreate the control file..with the create controlfile..script which has got new datafile..(the file I missed in the first attempt) .In the first attempt database was cloned without any problem .

Regards
Ajeet

May 16, 2006 - 11:46 am UTC

insufficient data here - suggest you utilize support and give them all of the details.

the entire chain of events would be sort of necessary to see if you can use this file or not. (and please don't put it here, it would not really be a followup to the original question)

Cloning

Swaminathan, May 24, 2006 - 6:37 am UTC

I found this document as a good education.

I am sorry if this question seems to be stupid.

1.I want to know what are the diffences between doing and export/import and doing DB Cloning.

2.If we do a database cloning from one unix box to another, do we need the mountpoints to be similar....if so whats the reason?

May 24, 2006 - 7:21 am UTC

1) yes, export import is just a "dump and reload".

cloning is usually "backup and restore" - physically bit for bit, byte for byte a image of the original system.

2) no, but it would obviously make it "easier", but it is not a requirement.

reader

A reader, June 16, 2006 - 2:45 pm UTC

Hi tom,

I just wanted to ask a simple question..like if i have two databases test and db01. i just need to do set ORACLE_SID=test or set ORACLE_SID=db01 so that i can run either of those database and do startup, WILL it automaticaly locate the init file of that one and startup the database whose sid is provided..is this true

Please guide

June 16, 2006 - 7:19 pm UTC

that is true, IF your pfile/spfile follows the standard name/location conventions for your platform.

startup pfile=<filename>

works as well

Cloning oracle database

Amol R Tambolkar, August 01, 2006 - 8:04 am UTC

Good One ! Is there any document available for cloning the oracle S/W?

August 01, 2006 - 10:13 am UTC

Enterprise Manager - it does that.

Thanks for saving me!

Gary, August 21, 2006 - 11:54 am UTC

I'm using JDBC with Oracle in a situation where there is not really an Oracle DBA. I did not know that SID and SERVICE_NAME were different, until I found your article. Thank you very much! You really saved me today.

got db name in datafile head not matching db name error( ORA-01161)

Sean, October 31, 2006 - 10:56 am UTC

Hi Tom,

I was trying to create second database using first database files. I followed
the note 15390.1. Oracle 9207. OS: Solaris 5.9. db is in noarchivelog mode.

Here is what I did:
(1) shutdown the first db and copied all datafile and logfile from //export/home/oracle/oradata/cbt1 to /export/home/oracle/oradata/cbt2 (first db name is cbt1)
(2) create second init file and modify instance name and db name to cbt2
(3) generate new control file script.
(4) But I got this error while run it:
ORA-01161: database name CBT1 in file header does not match given name of
CBT2
ORA-01110: data file 1: '/export/home/oracle/oradata/cbt2/system01.dbf'

Here is the new control file:

CREATE CONTROLFILE REUSE DATABASE "CBT2" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/export/home/oracle/oradata/cbt2/redo01.log' SIZE 100M,
GROUP 2 '/export/home/oracle/oradata/cbt2/redo02.log' SIZE 100M,
GROUP 3 '/export/home/oracle/oradata/cbt2/redo03.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/export/home/oracle/oradata/cbt2/system01.dbf',
'/export/home/oracle/oradata/cbt2/undotbs01.dbf',
'/export/home/oracle/oradata/cbt2/cwmlite01.dbf',
'/export/home/oracle/oradata/cbt2/drsys01.dbf',
'/export/home/oracle/oradata/cbt2/example01.dbf',
'/export/home/oracle/oradata/cbt2/indx01.dbf',
'/export/home/oracle/oradata/cbt2/odm01.dbf',
'/export/home/oracle/oradata/cbt2/tools01.dbf',
'/export/home/oracle/oradata/cbt2/users01.dbf',
'/export/home/oracle/oradata/cbt2/xdb01.dbf'
CHARACTER SET WE8ISO8859P1

It seems that the error makes sense, since the procedure never change the db name in the datafile head.

Thanks so much for your help.

Sean

October 31, 2006 - 11:00 am UTC

see steps 7 on in the above under Changing the "db_name" for a Database:

and also realize, you don't NEED to.

What is the difference between cloaning and physical standby..?

NK, October 31, 2006 - 2:43 pm UTC

Hi

What would u suggest...?

October 31, 2006 - 4:17 pm UTC

I don't know, we'd have to find the mystical "U" person (anyone happen to know if that is a male or female name? I've been curious for a long time).

Would you like my input?

My input would be: what are your NEEDS, GOALS. What are you trying to achieve. What do you need to do?

Do you need a disaster recovery site? Then data guard with physical standby makes sense.

Do you need a testing copy of your production database? Then maybe a clone makes sense.

You'd have to say what it is you need to accomplish - not list a bunch of ways to accomplish stuff - in order to get a sensible recommendation about anything.

create clones of db

sean, October 31, 2006 - 2:44 pm UTC

It works after I changed "reuse" to "set"

run scripts during database creation

sreeraj.s, January 31, 2007 - 1:44 am UTC

Hi Tom,
I like to know y r v runin the pupbld.sql script connected as system/manager.
v know 'sys' is the super user wid al privs n then y r'nt v not runnin as sys?
do u have any answers regarding this about connecting as system/manager
If it is to reduce the burden on 'sys', wat that really meant by( the burden)?
i was not given any errors when i conected as sys although connecting thrugh 'sys' can create the profiles..
so y du v connect as system/manager to run only this script where we run the catalog n catproc as sys?

January 31, 2007 - 10:05 am UTC

one word for you here:

huh?

pupbld is a somewhat archaic script that provides some functionality for an application called sqlplus. It is not part of the database proper in that sense, it is just some application data.

And the people that wrote sqlplus picked system as the owner of these tables. Because these tables are not part of the database dictionary.

manoj, February 12, 2009 - 7:28 am UTC

i have 4 database namely 'vat','tax','hcs','store'
i can't connect using system/manager@tax ,@hcs and @store
i will get error ora-01017 error
but i can connect with the system/manager@vat

in the services.msc these database are started

what will be solution please help me

February 12, 2009 - 3:53 pm UTC

ummm, well...

$ oerr ora 1017
01017, 00000, "invalid username/password; logon denied"

manager is not the password for system on those databases....

Move database to new server via cold bkup

John, March 19, 2009 - 5:34 pm UTC

Tom

I am migrating the 10.2.0.4 database (running in NOARCHIVELOG mode) to new server using cold backup (shutdown normal).

During this server move, i also need to do the following:

a)rename the SID and database name.
b) change the location of datafiles and logfiles to reflect the new server file location.

Following are the steps i came up with:

1. update the init parameter file with new dbname
2. startup nomount
3. recreate the controlfile with new dbname using (old server database controlfile trace file)
4. alter database open
5. add temporary tablespace tempfile

My question:

Should i use "RESETLOGS or NORESET LOGS" while recreating the controlfile. ?

CREATE CONTROLFILE SET DATABASE "RDPSRPD" NORESETLOGS NOARCHIVELOG

OR

CREATE CONTROLFILE SET DATABASE "RDPSRPD" RESETLOGS NOARCHIVELOG

After controlfile creation, Should i use "alter database open resetlogs or just alter database open".

Thanks for your time.

March 24, 2009 - 10:19 am UTC

you should be able to just 'open' if you copied the closed online redo logs (which you don't need to have done - but if you did...)

move database to new server with different location

Brian, March 24, 2009 - 8:07 pm UTC

Thanks Tom.
I have the question along the same line.

I need to move the database to a new server but no need to change the database.

I have a valid cold backup taken from the source server.

But Source and target server database locations are different.

could you please correct my migration procedure if anything wrong.

this is my migration procedure:

1.copy the database files/redologs files/controlfiles to the new location on the target server

2.copy the init.ora file and update the control file location to reflect the new location

3. startup mount pfile

4. rename all the datafiles/log files to reflect the new location

5. alter database open.

Question : Will i be able to mount the database with old control file (from cold backup) in the first place ?

Which one is better, "recreate controlfile" method or "rename datafile" method ?

Thanks

March 29, 2009 - 2:22 pm UTC

rename datafile would be 'better' as it would leave any information in the control files intact.

since you do not backup redo logs during a cold backup (you can only hurt yourself if you do), you wouldn't have them to restore, so you would open resetlogs but other than that - sure, looks OK at a glance.

A reader, September 29, 2009 - 9:34 pm UTC

Though it is an eye opener ........

Murali Vanamamalai, May 11, 2010 - 9:53 am UTC

Oracle should really consider making an easy online SQL command to change the SID like ALTER DATABASE .....

Your procedure shows 10 steps + 15 steps so a total of 25. Thanks for providing the details, which is very useful though not enjoyable.

Cloning from 11gr1 on Vista to 11gr2 on Linux

Haris, September 12, 2010 - 7:10 am UTC

Hi Tom,

How can I make a db clone from 11gR1 running on Windows Vista machine to 11gR2 running on CentOS 5.5. I ask this because my Windows Database got crashed after installing Apex. I have the data files.

again change dbname

ashish surana, May 18, 2011 - 2:28 am UTC

Hi Tom,

I have a problem, i changed dbname using nid utility,first time it's change but when i again change dbname of the same db, it create error not allow to change dbname which was i changed first time. can you tell me how to solve this problem.

May 18, 2011 - 10:08 am UTC

you don't need to use nid to change a dbname, you use that to change a database id basically.

so.... I'm not sure what you did or what error you are hitting. be a bit more detailed in your list of 'steps' please.

Backup of Oracle Home

rizwan, July 21, 2011 - 7:45 am UTC

Hello Tom,

Can we take a backup of Oracle Home (using cp on linux)
online (while the databases and listener in that home is up and running ) or
do we need to stop all oracle procesess running on that oracle home and than take backup ?

Please elaborate .

I know this is not the right thread to put this question but i don't know where should i put .

July 22, 2011 - 1:54 pm UTC

I would hardly call "cp" a backup - don't you have a real backup tool for your filesystems? cp can get easily stuck on things like named pipes and other things - it really isn't a 'backup' tool

In short, as long as none of your datafiles are in that backup, it is probably OK, your log and trace files might be funny looking upon restore - but it should be OK.

But it still isn't the right thing to do.

Backup of Oracle Home

rizwan, July 25, 2011 - 12:27 pm UTC

Thanks for your response .

We don't have any datafile or archived redo log files in that directory . Its just an ORACLE Home .. It was like Unix team wanted to extend that mount point and they told me to take backup of ORACLE Home so in case anything went wrong we can restore from the backup ..

So with your answer i suppose backup taken using cp is restorable in that case .. You said you should backup using backup tool .. Could you give example of one such tool ? What are the advantages of using backup tool instead of cp ?

July 27, 2011 - 8:08 pm UTC

cp is NOT a backup tool

cp MIGHT result in something you can restore from, it might not.

there are thousands (or millions) of backup tools for your OS, talk to your OS vendor.

the advantages of using a backup tool is that you have.... a backup.

using cp, you might have something you can get away with, you might not.

why not ask your system administrator to take care of this? If they have the same questions as you - you need a new system admin ;) Backing up is not hard, you do however need to know what to do... and how to do it.

Creaying Database on CentOS5.8

Nilesh Kumar, January 14, 2013 - 2:04 am UTC

Hi Tom,
I am creating a new DB in CentOS 5.8, but am a bit confussed about Environment variables, and the way it is trated by OS and Oracle (Database Software and Instance)

1. The Environment variables are not persistant, i.e. they become unset once the user session (in OS) is logged out. So what if, once the instance is started by OS User oracle, and the user logs-off. will the instance (or any Background process) look for these values.

2. How can i make these environment variable persistant across session (in OS) other then making an entry in .profile file (since it is only executed after user logs in) so that these values are always set whenever the system reboots, even before any user logs-on.
3. if i make use of oracle restart service, will the instance be started once the machine is up but no user (including root or oracle) has logged into the system. And in this case, from where will the oracle look for environment variables (since .profile is still not executed) and what will be their values.
4. And what will be the value of ORACLE_SID for a RAC Cluster environment.

I Know my question relates more to OS then to Oracle itself, but as i was not able to find these answers. Also it will be really helpful if you can provide a link for expanation of environment variables in details.

January 15, 2013 - 10:21 am UTC

1) this is really an OS question - but simple enough to answer.

the environment of any process is inherited from the process that created it. So, if you log in, set some environment variables, then start something - it's initial environment will be whatever you had set. It is then free to overwrite that environment, change it, unset things whatever.

so the instance will have inherited its initial state from the shell that started it.

2) doesn't work that way in unix, you don't do it that way. you should never rely on your environment being set a certain way (big security risks in that as well as hard to debug issues). be explicit, set your environment to what you know it needs to be.

3) everything starts from process 0, the environment will come from there - and each process that is forked off will add it to.

so if you have a start up script to startup your database - you would be the setting of the environment in there - anything forked by that script would get that environment.

4) depends on the context. you typically don't use SID in RAC, you use services to connect to the database.

http://en.wikipedia.org/wiki/Environment_variable

Any side-effects of changing DB NAME using NID utility

Robert, May 09, 2014 - 4:33 pm UTC

Hi Tom,

Is there any downside or unexpected side-effects of changing the DB NAME (only) using the NID utility?

We recently migrated a database from one server to another by using the rman duplicate for standby, syncing is up, then breaking it off on its own as an independent database using 'activate standby database'.

So far so good... but the DB_NAME initialization parameter still shows the old db name (db_unique_name is the new one and that is how everyone is accessing it).

So for consistency and to avoid any confusion down the road, I want to change the DB NAME to be the same as the DB_UNIQUE_NAME (which includes changing the global_name in the global_name table as well).

Is there any possible "under the hood" unexpected downside or any side-effects from making such a change. (we are *not* changing the DBID... only the DB NAME).

Thank you,

Robert.

This is a good joke

Michel Casabianca, June 24, 2016 - 9:02 am UTC

Is Oracle serious about that?

How to get database name without execution query like v$Database

A reader, July 06, 2016 - 11:16 am UTC

How to get database name in oracle db in node js without query using connection object

More detail
http://stackoverflow.com/q/38221827/4662688

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.

What is a SID, how to change it, how to find out what it is.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

oracle dba

how many DBAs oracle needs to change a light bulb

How long should this take

Great answer

dba

SID

How did you connect in the first place?

soooo cool!

Thanks Mr. Tom

how to recreate the controlfile in Oracle9i

when and how is the DBID generated?

Cloning an Open database

Cloning across OS

May be in 10G

I'm feeling tidly winky now....

I meant for cloning on 9i Rel 2 or earlier versions.

Thanks...

what happens step by step when alter database open resetlogs

Good

INSTANCE_NAME on RAC

if $ORACLE_SID <> INSTANCE_NAME ...

Cloning using RMAN

clone need (usualy but not always) the same OS

What is a SID, how to change it, how to find out what it is

Thanks

Sorry for Typing Mistake Tom

change log source before duplicate command

Export-Import won't do it?

Thanks

Thanks

Next step pls.

Changes made to rename sid

another question on cloning

just to add one more detail in my above question

Cloning

reader

Cloning oracle database

Thanks for saving me!

got db name in datafile head not matching db name error( ORA-01161)

What is the difference between cloaning and physical standby..?

create clones of db

run scripts during database creation

Move database to new server via cold bkup

move database to new server with different location

Though it is an eye opener ........

Cloning from 11gr1 on Vista to 11gr2 on Linux

again change dbname

Backup of Oracle Home

Backup of Oracle Home

Creaying Database on CentOS5.8

Any side-effects of changing DB NAME using NID utility

This is a good joke

How to get database name without execution query like v$Database

More to Explore

Backup/Recovery