Ganesh, June 17, 2001 - 9:25 am UTC
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
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.
Joel, September 14, 2001 - 11:00 am UTC
Thanks, Tom....this is something that has always puzzled me.
lucas mashele, November 15, 2001 - 3:00 am UTC
Ofoe, February 16, 2002 - 9:52 am UTC
Can you an example in changing the SID on WINDOWS 95/98 platforms?
February 22, 2002 - 9:32 am UTC
an, February 22, 2002 - 10:20 am UTC
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
$ 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.
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
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
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
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
When i use clean cold backup to do the database cloning, i found that no matter i use
1) create control file REUSE database
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
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) ?
June 30, 2003 - 9:20 am UTC
Cloning across OS
Ma$e, August 13, 2003 - 11:10 am UTC
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 ?
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 ;) ;)
I'm feeling tidly winky now....
Ma$e, August 13, 2003 - 3:10 pm UTC
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
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
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
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.
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
2 l_string varchar2(255);
3 l_dummy number;
5 l_dummy := dbms_utility.get_parameter_value( 'instance_name', l_dummy, l_string );
6 dbms_output.put_line( l_string );
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:
Database is using spfile. In an init.ora file ($ORACLE_HOME/dbs/i1.ora), I have:
Please notice instance_name.
Now, I run SQL*
> sqlplus 'sys/sys as sysdba'
SQL> startup pfile=$ORACLE_HOME/dbs/i1.ora
SQL> show parameters instance_name
name ... value
INSTANCE_NAME db9i1 --???
SELECT instance_name FROM v$instance;
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
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.
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
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
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?
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.
A reader, November 06, 2004 - 8:23 pm UTC
The answer was really helpful.
Sorry for Typing Mistake Tom
A reader, November 06, 2004 - 8:24 pm UTC
change log source before duplicate command
A reader, November 09, 2004 - 11:58 am UTC
188.8.131.52 on HP-UX
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..
Export-Import won't do it?
Mark, November 10, 2004 - 10:32 pm UTC
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,
(6) And all users are ready to access new database with new
Thanks for your help,
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)
A reader, November 12, 2004 - 1:25 am UTC
Thanks for your help Tom.
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
What I have done is as follows upto now:-
1) Installed software without installing database on server
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,
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
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)
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
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..
just to add one more detail in my above question
Ajeet, May 16, 2006 - 11:34 am UTC
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 .
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)
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.
A reader, June 16, 2006 - 2:45 pm UTC
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
June 16, 2006 - 7:19 pm UTC
that is true, IF your pfile/spfile follows the standard name/location conventions for your platform.
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
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
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
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
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.
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
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
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:
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
$ 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
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
Should i use "RESETLOGS or NORESET LOGS" while recreating the controlfile. ?
CREATE CONTROLFILE SET DATABASE "RDPSRPD" NORESETLOGS NOARCHIVELOG
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
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 ?
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
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
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
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
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
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).
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