Breadcrumb

Announcement

Forty years

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, ben.

Asked: May 12, 2001 - 1:22 pm UTC

Answered by: Tom Kyte - Last updated: July 16, 2009 - 11:32 am UTC

Category: Database - Version: 8.1.5

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Multi-table insert

You Asked

Tom

You had previously answered this question for our unix users can you pls describe the procedure for the NT folks.

Will the same procedure be valid for NT ?

thx

Ben



and we said...

Article-ID: <Note:61590.1>
Alias: OLS:11234946.61
Circulation: PUBLISHED (EXTERNAL)
Folder: server.DBA.Admin
Title: Renaming an existing Windows NT database
Document-Type: BULLETIN
Impact: MEDIUM
Skill-Level: NOVICE
Server-Version: 07.02.0X to 08.01.0X
Updated-Date: 09-MAR-2001 09:40:06
References:
Attachments: NONE
Content-Type: TEXT/PLAIN
Keywords: DATAFILEINFO; WINNT;
Products: 5;

PURPOSE
This bulletin outlines the steps required to rename an existing Oracle
Database on the Windows NT platform, including the SID, DB_NAME,
and all files associated with these.

SCOPE & APPLICATION
This note is intended for all DBAs.


===============================================================================

These instructions assume the following:

- your database is started
- you are on the Server
- the existing database SID is ORCL and DB_NAME is ORACLE
- the new database SID will be TEST and DB_NAME will be TEST

For database version 7.2 use sqldba72 and oradim72
7.3 use svrmgr23 and oradim73
8.0 use svrmgr30 and oradim80
8.1 use Svrmgrl and oradim

This document uses an 8.0 database, but these instructions can easily be
translated to the another version by changing the utility name.


---------------------------------------------------------------
WARNING:
Before proceeding, you should take a valid full backup.
You should also read the entire bulletin before you attempt to
use it.
---------------------------------------------------------------


1. Back up the controlfile to trace:

C:\> SET ORACLE_SID=ORCL
C:\> svrmgr30
SVRMGR> CONNECT INTERNAL/<internal_password>
Connected.

Now, we need to verify the user_dump_destination. This can be done
in two ways:

SVRMGR> SHOW PARAMETER USER_DUMP_DEST

NAME TYPE VALUE
---------------- ------- ------------------------------
user_dump_dest string D:\ORANT\rdbms80\utrace

** OR **

SVRMGR> SELECT value from v$parameter where name = 'user_dump_dest';

VALUE
----------------------------------
D:\ORANT\rdbms80\utrace


Create the dump file copy of the controlfile:

SVRMGR> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

This will create a dump file, ORAxxxxx.TRC, in the user_dump_destination.


2. Verify which files your database contains:

SVRMGR> select name from v$datafile;

NAME
-----------------------------------------
D:\DATABASES\ORCL\SYS1ORCL.ORA
D:\DATABASES\ORCL\RBS1ORCL.ORA
D:\DATABASES\ORCL\USR1ORCL.ORA
D:\DATABASES\ORCL\TMP1ORCL.ORA
D:\DATABASES\ORCL\INDX1ORCL.ORA
D:\DATABASES\ORCL\OEM204.DBF
D:\DATABASES\ORCL\USR2ORCL.ORA
D:\DATABASES\ORCL\TEST.DBS
D:\DATABASES\ORCL\WEBDB.DBF


3. SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE the database and EXIT.


SVRMGR> shutdown immediate


4. Stop the services:

Using Control Panel/Services - scroll down to OracleServiceORCL and
click on the STOP button. You might be asked if you want to stop the
OracleStartORCL-service too. If so, select yes. Please note that under
Oracle 8i, only the OracleServiceORCL will exist.

Or type at the command prompt:

C:\>net stop OracleServiceORCL

The following services are dependent on the OracleServiceORCL service.
Stopping the OracleServiceORCL service will also stop these services:

OracleStartORCL

Do you want to continue this operation? (Y/N): y

The OracleStartORCL service was stopped successfully.

The OracleServiceORCL service is stopping.
The OracleServiceORCL service was stopped successfully.
C:\>


5. Generally, the database files contain the SID name; if you want to change
this, use Explorer, File Manager or DOS, and rename the files listed in
step 2 to the appropriate filenames.

If you want to files to be placed elsewhere, copy the files to the new
Location.

Under Oracle8I, the default directory structure is such that :

D:\Oracle\oradata\ORCL - will contain control files, redo logs, datafiles

D:\Oracle\admin\ORCL\pfile - will contain the init file.

These files typically will not contain the <SID> name as part of the filename.

Example: The init file for the <SID> of 'LEO' will be:

D:\Oracle\admin\LEO\pfile\init.ora - with the directory structure defining the <SID>.

Reference the Getting Started Oracle8I for Windows NT manual for more information
Regarding the Oracle8I default directory structure.


6. Create and Edit the INITTEST.ORA:

a. Copy the %ORACLE_HOME%\database\INITORCL.ORA to
%ORACLE_HOME%\database\INITTEST.ORA
b. Replace all occurrences of the string ORCL with TEST and modify all
necessary directories to the correct path.
c. Modify DB_NAME = ORACLE to read DB_NAME = TEST
d. Save the file.

7. Edit the dump file created in Step 1:

a. Change all occurrences of the string ORCL to TEST.
b. Remove the header (delete all lines above the STARTUP NOMOUNT)
c. Modify the Startup line to read:

STARTUP NOMOUNT PFILE=%ORACLE_HOME%\DATABASE\INITTEST.ORA

In our example we would use:

STARTUP NOMOUNT PFILE=D:\ORANT\DATABASE\INITTEST.ORA

d. Change the NAME of the database. Modify the CREATE CONTROLFILE:

replace REUSE DATABASE "Oracle" with SET DATABASE "Test"
replace NORESETLOGS with RESETLOGS

e. Remove the lines

# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;

NOTE: the ALTER SYSTEM ARCHIVE LOG ALL is only present when the original
database is in ARCHIVEMODE.

f. Save the file to CHNAME.SQL


=========================================================================

Example:
========

STARTUP NOMOUNT PFILE=D:\TEST\DATABASE\INITTEST.ORA
CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 (
'D:\DATABASES\TEST\LOGTEST1.ORA',
'D:\DATABASES\TEST\CTLBIS\CTL1TESTBIS.ORA'
) SIZE 2M,
GROUP 2 (
'D:\DATABASES\TEST\LOGTEST2.ORA',
'D:\DATABASES\TEST\CTLBIS\CTL2TESTBIS.ORA'
) SIZE 2M
DATAFILE
'D:\DATABASES\TEST\SYS1TEST.ORA',
'D:\DATABASES\TEST\RBS1TEST.ORA',
'D:\DATABASES\TEST\USR1TEST.ORA',
'D:\DATABASES\TEST\TMP1TEST.ORA',
'D:\DATABASES\TEST\INDX1TEST.ORA',
'D:\DATABASES\TEST\OEM204.DBF',
'D:\DATABASES\TEST\USR2TEST.ORA',
'D:\DATABASES\TEST\TEST.DBS',
'D:\DATABASES\TEST\WEBDB.DBF'
;

===========================================================================

8. Use ORADIM80 to create the new instance with the new SID. From a DOS
Command Prompt type:

ORADIM80 -new -sid TEST -intpwd password -startmode auto -pfile %ORACLE_HOME%\DATABASE\INITTEST.ORA


9. Verify that the new service is started:

a. From in Control Panel/Services, scroll down to OracleServiceTEST.
b. If not started, click on the START button.


10. Executing the "chname.sql" script:

C:\> SET ORACLE_SID=TEST
C:\> svrmgr30

SVRMGR> CONNECT INTERNAL/internal_password
Connected.
SVRMGR> @<user_demp_destination_path>\chname.sql
#or the directory where you saved your chname-script.

This will recreate the controlfile with the new datafiles....


11. Open and Shutdown the database.

SVRMGR>ALTER DATABASE OPEN RESETLOGS;
statement processed

Verify at this point if the database is open:

SVRMGR>SELECT status FROM V$THREAD;

STATUS
------
OPEN

SVRMGR>SHUTDOWN IMMEDIATE;


12. Stop the TEST service and take a full backup

a. From in Control Panel/Services, scroll down to OracleServiceTEST.
b. Click on the STOP button.
c. Take a full backup of your database.


13. Removing the old-instance:

See <Note:62004.1> Removing an Oracle Instance and Database from
Windows NT

Please note:

a. If you reused the same files or you didn't rename them (i.e. you
skipped Step 2 partially or completely), don't remove these files
when following Step 6 of <Note:62004.1>

b. The instance removed in <Note:62004.1> is called TEST, which is
our new instance; you must replace TEST by ORCL in <Note:62004.1>
if you want to follow the instructions in <Note:62004.1> to the
letter.

Also, be sure to check your NET8 or SQL*Net setup for references to ORCL and
TEST (i.e. TNSNAMES.ORA, LISTENER.ORA, etc.).


----------------------------------------------------------------------------
Oracle Support Services



and you rated our response

  (43 ratings)

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

Reviews

July 14, 2001 - 10:37 am UTC

Reviewer: J maruti from VA USA

I liked the way article was written and also enjoyed reading and following the instructions. They are very clear.

renaming a database and sid in NT

August 08, 2001 - 11:15 pm UTC

Reviewer: Shajan Joseph from Chennai, India

The step by step instructions is very useful

renaming a database and sid in NT

August 27, 2001 - 3:31 pm UTC

Reviewer: Sunny from Columbus, OHIO USA

This article helped me out big-time . This also applies to cloning your database. THanks Tom

renaming a database - followup

January 16, 2002 - 11:59 am UTC

Reviewer: Ranajit

I want to create the control file using sql script on the fly and hence want to know where to look for MAXDATAFILES, MAXINSTANCES in the database ?



Tom Kyte

Followup  

January 16, 2002 - 3:14 pm UTC

just use

alter database backup controlfile to trace;


we create the create controlfile statement on the fly for you. Look in your user_dump_destination after running that for the statement.

Error ORA-1161

January 17, 2002 - 12:01 pm UTC

Reviewer: Vladimir from NY,US

Very clear outline however I got the above error. It states that data file headers stil contain old SID. Could you elaborate how to rectify this. P.S. I renamed them to SYS1new_name.ora and etc... It is my fist time post and I would like to join many people showing my appreciation of the work you do! Thanks.

Tom Kyte

Followup  

January 17, 2002 - 12:11 pm UTC

> oerr ora 1161
01161, 00000, "database name %s in file header does not match given name of %s"
// *Cause: The database name given at the command line does not match the
// database name found in the file header.
// *Action: Chance are good that the database name specified at the command
// line is incorrect. Resolve the descepency, and resubmit the command.
// If you are attempting to change the database name, be sure to
// use the SET DATABASE option.

I believe you skipped step 7d



Excellent !

January 18, 2002 - 2:55 am UTC

Reviewer: Magnus Herrstrom from Sweden

In most manuals and books they say that this can't be done or recommend not to do it. But once in a while you need to do it to save a lot of time and work.

January 18, 2002 - 2:11 pm UTC

Reviewer: A reader

Sir,

In step No. 13 you have mentioned

"See <Note:62004.1> Removing an Oracle Instance and Database from Windows NT"

How could I find this article ?

Tom Kyte

Followup  

January 19, 2002 - 10:00 am UTC

Using </code> http://metalink.oracle.com/ <code>-- the Oracle Support web site (whence this note originated)

What do I use in place of "Svrmgrl" in 9i ?

January 29, 2002 - 2:13 pm UTC

Reviewer: Ravi from NY,NY

Oracle 9i does not have Svrmgrl ? What can I use in its place ?

Thanks!

Tom Kyte

Followup  

January 30, 2002 - 7:09 am UTC

sqlplus replaces svrmgrl in 9i.

RE: What do I use in place of "Svrmgrl" in 9i

January 30, 2002 - 9:21 am UTC

Reviewer: A reader

What do you do in place of reading the Release Notes and documentation? This is only one of the most publicised changes in 9i...

RE: RE: What do I use in place of "Svrmgrl" in 9i

January 30, 2002 - 9:25 am UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

Re: What do you do in place of reading the Release Notes...

Not only that, but simply putting "svrmgrl 9i" into the AskTom search engine would have easily answered the question as well.

The AskTom search engine must be one of the most under-utilized tools around...

Sorry folks, just getting my feet wet in 9i

January 30, 2002 - 9:50 am UTC

Reviewer: Ravi from NY,NY

.... I will wake up early and work hard now on :)

How can I change the default from ORCL for SQL*Plus

June 22, 2002 - 2:07 pm UTC

Reviewer: Ravi Kumar from IL, USA

Hi,

I installed 9i on my Win2000 machine. Created a database with sid HOME. When I use SQL*Plus to connect database, without the connect string(scott/tiger), it connects. I created one more database OEMREP for Enterprise Manager. After that, when I just give scott/tiger, it is connecting to the new database OEMREP. And I am able to connect to both the databases successfully using @HOME or @OEMREP.

I deleted this new database and when I try to connect with scott/tiger, I am getting "TNS:Protocol error". I checked sqlnet.ora, it is trying to connect to sid ORCL.
====
Fatal NI connect error 12560, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=C:\oracle\ora92\bin\sqlplusw.exe)(HOST=MYHOME)(USER=Administrator))))

VERSION INFORMATION:
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 9.2.0.1.0 - Production
Time: 22-JUN-2002 13:05:20
Tracing not turned on.
Tns error struct:
nr err code: 0
ns main err code: 12560
TNS-12560: TNS:protocol adapter error
ns secondary err code: 0
nt main err code: 530
TNS-00530: Protocol adapter error
nt secondary err code: 2
nt OS err code: 0
===

where is it being set for SQL*Plus executable and how can I change that?

Thanks,
Ravi.

Tom Kyte

Followup  

June 22, 2002 - 2:10 pm UTC

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

Just set an environment variable of your choosing. You can set this in a script (bat/cmd file) or using control panel once and for all.

Cloning is Diff for Oracle Applications

December 09, 2002 - 12:12 pm UTC

Reviewer: Sikandar Hayat Awan from Pindi Bhattian - Pakistan

The thread is very useful but this procedure is not useful for Oracle Applications.

I want to ask Mr. Tom that is there any site like askTom covering Oracle Applications(OF) other than Metalink?

Tom Kyte

Followup  

December 09, 2002 - 1:01 pm UTC

Not that I am aware of, no.

v9i

December 28, 2002 - 1:40 am UTC

Reviewer: Anton from Phil.

Hi Tom,

Is this applicable to v9iR2??

Tnx

Tom Kyte

Followup  

December 28, 2002 - 9:36 am UTC

more or less -- the names of oradim are different (just oradim), you might be using an spfile instead of a pfile but in general, the steps are the same.

goes without saying of course if you do this without a full, tested backup -- you are on your own.


SID and db_name

March 31, 2003 - 8:51 am UTC

Reviewer: Steve from UK

Is it possible to have 2 instances on one server with the same db name but different sid's?

I know it's probably asking for trouble but I'd like to know if it's possible all the same!

Tom Kyte

Followup  

March 31, 2003 - 9:25 am UTC

well, an instance does not have a dbname -- a database does -- but I understand..

sure it is.

You can even have 2 instances with the same SID on the same machine that mount two separate databases that have the same dbname's.

DBMS_JOB AND CLONING

June 13, 2003 - 10:52 am UTC

Reviewer: Sachin from Germany

8.1.7.4
is there a way to make database "not" to run any of the jobs..while cloning databases...using RMAN backups to clone.

Thanks

Tom Kyte

Followup  

June 13, 2003 - 11:09 am UTC

it would not be a "clone" then.

you would have to set job_queue_processes=0 in the init.ora of the cloned database (alter system can do that on the fly)

this is wonderful!!!

June 14, 2003 - 5:54 am UTC

Reviewer: anurag

What a wonderful explanation. Tom, what's a secret of yours answering each question with so much of patience. Is it UR idea to start with asktom.... Can we know its history...



MAXDATAFILES

July 02, 2003 - 9:29 am UTC

Reviewer: Abdul Wahab from Jeddah

Dear Dear Tom,
I need to change the MAXDATAFILE parameter. The database is 7.3.4 and it has hit the maximum and we need to increase that parameter. I need to hcange ONLY this parameter and nothing else. h o w ?
Should i follow the above method for changing the control file? if yes what about that 'reuse' command in control file.
Please reply soon as i am going to work on this database to which 150 users are connected.Can't tolerate down time.
Thanks

Tom Kyte

Followup  

July 02, 2003 - 10:39 am UTC

you need to rebuild your controlfiles (sort of oxymoronic -- "i cannot have down time, yet I run unsupported software")


0) BACKUP. make sure you have a backup. make sure you can restore this backup. then....

1)
SVRMGR> connect internal
Connected.
SVRMGR> alter database backup controlfile to trace;
Statement processed.
SVRMGR>


2) cd to your user dump destination directory. find the newest trace file, edit it

3) remove the trace file header, change maxdatafiles, save as "test.sql" or something.

4) shutdown

5) move your old controlfiles elsewhere

6) use test.sql to startup the database, the controlfiles will be recreated for you.



if you skip step 0, you are on your own....



i forgot to mention

July 02, 2003 - 10:07 am UTC

Reviewer: A reader

the operating system is unix tru64

i forgot to mention

July 02, 2003 - 10:07 am UTC

Reviewer: abdul wahab from jeddah

the operating system is unix tru64

OS server migration

July 02, 2003 - 2:32 pm UTC

Reviewer: Saradha Bavanandam from NJ, USA

Tom,

We are planning to replace the existing HP server
(HP V2500) with the new HP superdome server.

To minimize the impact on the production, we are planning
to take the DASD from the old server and plug it into the
new server. I am not an unix admin but this is what the
UNIX admins are planning to do.

There are 10 oracle9i instances running On the old server
and there is a procedural gateway for 4 oracle instances
to communicate with the mainframe CICS transactions.
This is using SNAPlus2 network architecture.

The new server will have a different HOST NAME and
different IP address. My assumption is that, since they
are switching the DASD, the oracle instances will not be
re-created in the new server and they are just brought over
to the new server with all the data.

If I am clear in what I am trying to tell you, what will
be the impact of this new HOST NAME and new IP address
on these Oracle instances and as well as on the
Procedural Gateway in the new server.

The TNSNAMES.ORA and LISTENER.ORA files will be modified on
the new server to reflect theses but other than that what
else is to consider.

Please give your thoughts...

Thanks
Saradha

Tom Kyte

Followup  

July 02, 2003 - 2:38 pm UTC

well, they better TEST THIS stuff eh?

they are changing OS's -- at the very least, they will have to relink Oracle.

10 instances on a single server? thats 9 too many.


For the database, the tnsnames/listener files pretty much cover it, as long as the mount points are the same.

It's done

July 05, 2003 - 8:29 am UTC

Reviewer: Abdul Wahab from Jeddah,K.S.A

Dear Tom,
I have done it.I changed the MAXDATAFILE parameter.
Thanks to your documentation and followup which was
very very very (..infinite) helpful for me.
Thanks a lot for being there. (and sorry for oxymoronic remark ;-)
take care

server migration

July 08, 2003 - 11:17 am UTC

Reviewer: Saradha Bavanandam from NJ, USA

"well, they better TEST THIS stuff eh?
they are changing OS's -- at the very least, they will have to relink Oracle.10 instances on a single server? thats 9 too many.
For the database, the tnsnames/listener files pretty much cover it, as long as the mount points are the same."

Tom,

We have received our new HP Server and the DASD
migration is going to happen only in September.

What kind of testing that you can suggest before hand
on the new server as far as oracle is concerned, so that
there are no surpises on the migration day.

Other question is:
There are DB LINKS created to these instances from other
external databases and do they need to re-create their DB
LINKS after the migration because the HOST name and IP address are going to be changed now.

Thanks
Saradha

Tom Kyte

Followup  

July 08, 2003 - 1:07 pm UTC

you should test your applications functionality and scalabilty. automated testing tools like LoadRunner can be very handy for that.


You can update the tnsnames.ora file to repoint to new ip/hostnames.

ORA-12560 Error when running svrmgrl on WIN NT

August 30, 2003 - 10:05 am UTC

Reviewer: A reader

I have installed Oracle 8i on Windows 2000 without the default database. I want to create database through database configuration assistant or through sqlplus. When I run svrmgrl it gives the error ORA-12560. Is it due to listener.ora, tnsnames.ora problem or any other thing. A good troubleshooting guide is required for this error.



Tom Kyte

Followup  

August 30, 2003 - 10:55 am UTC

fortunately -- we have a windoze administrator guide that goes through all of this!

you have to use oradim to create a service first on that platform.

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/win.817/index.htm <code>
see the windows admin guide

Getting clone of database running on another machine after crash

August 31, 2003 - 10:07 pm UTC

Reviewer: Jon Waterhouse from Newfoundland

Hi Tom,

I just had a crash of the NT machine that my test Oracle database was on: unfortunately have a lot of stuff there I don't want to lose. NT is restored on that machine enough to get at all the files, but something damaged is preventing Oracle running (the agent service fails, SQLplus cannot connect (just hangs). Was running in noarchivelog mode.

NT (and presumably the database) shut down fine, just NT failed on the next boot. I have tried copying all of the files in oradata (includes the control files and all of the data files) to another machine with Oracle (9i) installed (same disk and directory). If I follow the script above (with create control files) then I cannot open the database (ORA-01194: file 1 needs more recovery to be consistent).
a "recover using backup controlfile" says there is a change needed for file 1 that it suggests might be in a particular archive file (which of course does not exist).

If I try just a straight startup I get:
ORA-01991: invalid password file

Do either or these approaches have any chance of sucess if I persevere? Would copying the password file (and any other files) bring me joy, or am I doomed?

Thanks,

Jon

Tom Kyte

Followup  

September 01, 2003 - 8:12 am UTC

the database did not shutdown clean, else the file would not need recovery

have you tried "recover database"

you can use orapwd to create a new password file.

Changing IP Address of the 9i DB server

January 08, 2004 - 4:12 pm UTC

Reviewer: Irfan from Columbus, OH USA

Hi Tom,

I have to change the IP address of one of my 9i db server. Is it straight forward or do I have to follow some careful steps??

Tom Kyte

Followup  

January 08, 2004 - 8:17 pm UTC

you should just be able to "do it" unless you've used IP addresses in any configuration files.

Unless you put in fixed IP's, they'll have hostnames and you are not changing that so you should be AOK.


tnsnames.ora (clients and servers)
listener.ora (server)
init$ORACLE_SID.ora (for service registration) (or the spfile)

are the places to look

great article

February 09, 2005 - 5:27 am UTC

Reviewer: Vijay from India

Hi Tom,
good day to you, I would like some help on your comment "You can even have 2 instances with the same SID on the same machine that mount two separate databases that have the same dbname's." this may sound weird but is it possible in this case to have both the database up and running, how would the memory allocation be done in case it is possible.

Thanks as always for all your help.

Tom Kyte

Followup  

February 09, 2005 - 2:18 pm UTC

they would have different oracle homes

the oracle_home+oracle_sid is used to generate a key to the shared memory segment, so they would point to different SGA's just fine.

thanks a lot for your reply

February 10, 2005 - 12:50 am UTC

Reviewer: Vijay from India

Hi Tom,
good day to you, thanks a lot for your reply, if I get it correctly does this means that one can't have databases with same name and sid under one oracle_home, I should test this but presently I don't have a pc with me so just asking you.

Best Regards,
Vijay

Tom Kyte

Followup  

February 10, 2005 - 1:14 am UTC

correct, same sid under same oracle home would not work at the same time (they would want to use the same shared memory segment)

problem in 9i

May 27, 2005 - 11:15 am UTC

Reviewer: reader from India

Hi Tom,
I followed your instructions but I am stuck at at step 10, 9i onwards svrmgrl is not supported how do I connect to the database I know sqlplus has replaced svrmgrl but how should i get connected to execute the chname.sql, please help.

Kind regards,
your fan.

Tom Kyte

Followup  

May 27, 2005 - 12:35 pm UTC

sqlplus

thanks but still need help

May 27, 2005 - 1:10 pm UTC

Reviewer: your fan from India

Hi Tom,
I tried getting connected with sqlplus using options as
sqlplus /@test (i.e. db name)
sqlplus sys/password of old database@test as sysdba

but nothing worked how can I get connected to the database using sqlplus I am using Oracle 9i release 2, on Windows 2000 and not able to go ahead of step 10 as mentioned in your article, please help and sorry if I was not being clear enough in first place.

Tom Kyte

Followup  

May 27, 2005 - 1:18 pm UTC

sqlplus "/ as sysdba"

July 25, 2005 - 9:51 am UTC

Reviewer: alt from india

While creating database in oracle 9i using WINDOWS NT , After creating parameter file connecting to the idle instance(CONN SYS/AS SYSDBA) we are getting the following error

ORA-12560: TNS:protocol adapter error

How to Resolve this


Tom Kyte

Followup  

July 25, 2005 - 11:09 am UTC

sounds like the service is not running. you did run oradim to create a service and then started it (part of windows requirements here)

July 25, 2005 - 10:50 am UTC

Reviewer: A reader

Hi Tom,

I have a question about renaming SIDs. Our situation is like this: We have a database server with two separate RAID arrays. Our primary database is hosted on one of these arrays of disks. We have the other array as a backup. We also have a backup database running on a separate server. Now for the second RAID array, I want to know what will be the best way to utilize it and I want to know if the following is possible. Consider database with sid=DB1 on RAID1 and sid=DB2 on RAID2. If there is a problem with RAID1 and if DB2 and DB1 are in sync using some procedure which runs every 2 hrs or so, is it possible that I can stop OracleServiceDB1 and rename DB2 to DB1 and continue operations on the database without having to do a complete restore?

Thanks.


Tom Kyte

Followup  

July 25, 2005 - 11:17 am UTC

I'm not sure what raid levels have to do with anything at all? raid is totally transparent to us sitting on top of a file system.

But why would you need to rename a sid? You just need to have database 2 register itself as the same named service. clients don't use sids (they can, but don't have and shuold not)

July 25, 2005 - 11:22 am UTC

Reviewer: A reader

Hi Tom,

Can you explain me how to register database 2 as same named service?

Thanks.

Tom Kyte

Followup  

July 25, 2005 - 12:08 pm UTC

you don't want to do that simultaneously -- that would load balance between them and you want to do this as a failover.

when you start up db2, have it register dynamically as that service.

July 25, 2005 - 1:14 pm UTC

Reviewer: A reader

From my understanding I have listed the steps to be followed below. Can you review and comment on it please? Thanks a lot for your time.

1. Have database 1 running as always. Database 1 will be setup with datafiles on RAID1. SID=TEST
2. The same set of datafiles will be copied over to RAID2.
3. When there is a failure in RAID1, then dynamically register db2 as the same service SID=TEST (ie., using oradim, is that right?) and start using database 2 with same sid as database 1.

I have another question for you. How do I keep db2 in sync with db1 so that I can start using db2 in a failover scenario without any issues.

Thanks.


Tom Kyte

Followup  

July 25, 2005 - 1:35 pm UTC

raid levels, smaid levels, it is just disk... they are just disks to us.

are you looking for a disaster recovery solution??? I'm not 100% sure where you are going.

If you want disaster recovery, see:
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96653/toc.htm <code>


(no, oradim would not come into the picture, I was talking about dynamic registration of the DATABASE to a listener with a service name, but if you need disaster recovery, you want to read about data guard)

July 25, 2005 - 1:46 pm UTC

Reviewer: A reader

I need your help to understand what you mean by dynamically registering a database to a listener with a service name.

Thanks.

Tom Kyte

Followup  

July 25, 2005 - 2:25 pm UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/network.920/a96580/listener.htm#490288 <code>

Great job, shouldn't be necessary

December 17, 2005 - 4:49 pm UTC

Reviewer: Andy from Danbury, CT USA


Great instructions on how to move a database!

However, you would think that there would be a button that one could press while in an admin tool that would do all of this for you and create a single file that one could move to another machine and press another button to restore it. (Allowing you to move the database to a different drive or directory if you choose).

It is the fact that clear, well written thirteen step instructions like these are considered acceptable that has allow Microsoft with inferior products, with easier interfaces, to conquer the world.

Please, get a clue and automate this process, if only for databases less then 4GB in size!


Andy.


Tom Kyte

Followup  

December 18, 2005 - 10:09 am UTC

there is, this is how to do it "yourself"

moving to a different drive/disk - I would never do all of this, that is just move the files, alter database rename the files.


and if you wanted an "easy way" to move the database, the rman duplicate command might be of interest.


this is how to "rename" a database, a questionable thing to even want to do - but people get hung up on it - really wanting to rename something that isn't really exposed to anyone else.

Changing SID of database

June 01, 2006 - 3:38 pm UTC

Reviewer: Rahul from US

Tom,

What alterations will be in the process of SID renaming when you use 10G and use spfile.
If you could detail that or direct somewhere, would be great.

Tom Kyte

Followup  

June 01, 2006 - 4:10 pm UTC

pretty much the same.

rename the spfile instead of the init.ora file.

you might have issues if you put the sid name in the spfile itself. (parameters can be

sid.parameter = value
*.parameter = value

if you have sid.parameter, you'll need to reset them)

Oracle Not available

July 19, 2006 - 2:49 am UTC

Reviewer: Swaminathan from india

when i am trying to login to DB (Oracle 9.2.0.5) on windows NT server, i am getting the following error message:

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist:

Its been a long time since i have logged into this db and this is a test db
I would like to know, what are all the possible solution for the above scenario.

Tom Kyte

Followup  

July 19, 2006 - 9:06 am UTC

well, it sure looks like the database isn't started. start it.

Oracle not available

July 20, 2006 - 2:03 am UTC

Reviewer: Swaminathan from India

Well, i can recognise that the DB is not started, but the problem here is that i am not able to login to sql promt neither server manager.....when i do so i am getting the message 'oracle not avialable'

Now i need to know what are the required steps to be taken to start the DB???

Tom Kyte

Followup  

July 22, 2006 - 4:33 pm UTC

make sure the service is started (the oracle services)

and if the database wasn't installed to "autostart", you would have to:

sqlplus "/ as sysdba"
startup

to start it.

Different banaviour between binary and traced control file backup

October 25, 2006 - 4:02 am UTC

Reviewer: A reader from Singapore

HI, Tom,

I am confused by the following two DB recovery tests:
In both tests, I prepare the db as:
SQL> alter database backup controlfile to trace as 'c:\tt.sql' reuse;

Database altered.

SQL> alter database backup controlfile to 'c:\cf.bak' reuse;

Database altered.

SQL> alter tablespace my_ts add datafile 'C:\ORACLE\ORADATA\MYDB\MYTS2.DBF' size 128k;

Tablespace altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Delete all the control files and the newly added datafile MYTS2.DBF.

Test 1: Recover DB using binary control file backup "cf.bak"
  I copy cf.bak to the original control file location and rename as indicated in init.ora.

and run the following:
SQL> startup pfile='C:\oracle\admin\MYDB\pfile\init.ora' mount
ORACLE instance started.

Total System Global Area  135339940 bytes
Fixed Size                   454564 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> recover database using backup controlfile
ORA-00279: change 1092287 generated at 10/25/2006 10:19:16 needed for thread 1
ORA-00289: suggestion : C:\TEMP\MYDB_5.DBF
ORA-00280: change 1092287 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\oradata\MYDB\redo02.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 6: 'C:\ORACLE\ORADATA\MYDB\MYTS2.DBF'

ORA-01112: media recovery not started


SQL> alter database create datafile 6 as 'C:\ORACLE\ORADATA\MYDB\MYTS2.DBF';

Database altered.

SQL> recover database using backup controlfile
ORA-00279: change 1092393 generated at 10/25/2006 14:49:42 needed for thread 1
ORA-00289: suggestion : C:\TEMP\MYDB_5.DBF
ORA-00280: change 1092393 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\oradata\MYDB\redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

Please note that I have also restored the newly added datafile during the recovery process.

Test 2: Recover DB using traced output to re-create control file:
SQL> startup pfile='C:\oracle\admin\MYDB\pfile\init.ora' nomount
ORACLE instance started.

Total System Global Area  135339940 bytes
Fixed Size                   454564 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "MYDB" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 32
  3      MAXLOGMEMBERS 2
  4      MAXDATAFILES 100
  5      MAXINSTANCES 16
  6      MAXLOGHISTORY 1815
  7  LOGFILE
  8    GROUP 1 'C:\ORACLE\ORADATA\MYDB\REDO01.LOG'  SIZE 10M,
  9    GROUP 2 'C:\ORACLE\ORADATA\MYDB\REDO02.LOG'  SIZE 10M,
 10    GROUP 3 'C:\ORACLE\ORADATA\MYDB\REDO03.LOG'  SIZE 10M
 11  DATAFILE
 12    'C:\ORACLE\ORADATA\MYDB\SYSTEM01.DBF',
 13    'C:\ORACLE\ORADATA\MYDB\UNDOTBS01.DBF',
 14    'C:\ORACLE\ORADATA\MYDB\USERS_01.DBF',
 15    'C:\ORACLE\ORADATA\MYDB\MYTS.DBF',
 16    'C:\ORACLE\ORADATA\MYDB\TEST.DBF'
 17  CHARACTER SET US7ASCII;

Control file created.
SQL> recover database using backup controlfile
ORA-00279: change 1092488 generated at 10/25/2006 15:19:49 needed for thread 1
ORA-00289: suggestion : C:\TEMP\MYDB_5.DBF
ORA-00280: change 1092488 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\oradata\MYDB\redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

Please note that my newly added datafile is gone.

My question is, why the second test omits the new datafile? Is there any way for the second test to perform correctly to recover the newly added datafile?

Thank you 

error while creating database manually in 9i

May 14, 2007 - 1:45 am UTC

Reviewer: priyanka from india

hi tom,
i m creating a database manually in 9i and after creating password file i executed oradim to start services,fairly i did it successfully but still after being connected to an idle instance i m unable to start database in nomount state.i performed the following:
SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup nomount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'D:\ORACLE\ORA92\DATABASE\INITDB6.ORA'
SQL>
tell me what i m missing.
regards
priyanka
Tom Kyte

Followup  

May 14, 2007 - 2:02 pm UTC

you seem to be missing the file initdb6.ora?

change database name

August 07, 2007 - 3:06 am UTC

Reviewer: mohammad from amman/jordan

How can i change the database name ?

Also for Oracle 10.x or 11.x (SID CHange?)

July 16, 2009 - 9:30 am UTC

Reviewer: Stefan from Germany

Hi Tom,

following long time now your answers. Can I use this "CHANGE SID" guide also for DB 10 and 11?



Tom Kyte

Followup  

July 16, 2009 - 11:32 am UTC

what platform?