Changing DBID
Chuck
example in the case if a duplicated database, I want to reset the DBID
of the auxiliary DB to it's original DBID after the duplication is
complete to make maintenance on the old backups easier. The auxiliary
database in question is a test db, is not in archivelog mode, and all
it's backups are cold backups.
TIA
Tim Arnold
"Chuck" <chuckh19...@gmail.com> wrote in message
news:RKLck.1120$bn3.583@trnddc07...
see Metalink note Note:224266.1
Change Only the DBID
====================
1. Backup the database
2. SHUTDOWN IMMEDIATE of the database
3. STARTUP MOUNT
4. Open one session and run NID with sysdba privileges
% nid TARGET=SYS/password@test_db
5. Shutdown IMMEDIATE of the database
6. Set the DB_NAME initialization parameter in the initialization
parameter
file to the new database name
7. Create a new password file
8. Startup of the database with open resetlogsExample:
========
1. C:\>set ORACLE_SID=TEST1BY
C:\>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 24 11:16:52 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
2. check the DBID before change
SQL> select dbid,name,open_mode,activation#,created from v$database;
DBID NAME OPEN_MODE ACTIVATION# CREATED
---------- --------- ---------- ----------- ---------
1395399949 TEST1BY READ WRITE 1395404134 10-SEP-02
3. SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
4. SQL> startup mount pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL>exit
5. execute NID
C:\>nid target=sys/oracle@TEST1BY
DBNEWID: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Connected to database TEST1BY (DBID=1395399949)
Control Files in database:
D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL
Change database ID of database TEST1BY? (Y/[N]) => y
Proceeding with operation
Changing database ID from 1395399949 to 1397190693
Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL - modified
Datafile D:\ORACLE\BASE_TEST\TEST1BY\SYSTEM01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\UNDOTBS01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\DRSYS01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\EXAMPLE01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\INDX01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\TOOLS01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\USERS01.DBF - dbid changed
Datafile D:\ORACLE\BASE_TEST\TEST1BY\XDB01.DBF - dbid changed
Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL - dbid changed
Database ID for database TEST1BY changed to 1397190693.
All previous backups and archived redo logs for this database are
unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
6. SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
7. create the new passwordfile
8. SQL> startup mount pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
9. SQL> alter database open resetlogs;
Database altered.
10. check the new DBID
SQL> select dbid,name,open_mode,activation#,created from v$database;
DBID NAME OPEN_MODE ACTIVATION# CREATED
---------- --------- ---------- ----------- ---------
1397190693 TEST1BY READ WRITE 1397188261 10-SEP-02
NOTE: The NID change the OLD DBID 1395399949 to the NEW DBID 1397190693
Chuck
>
> see Metalink note Note:224266.1
So how do you specify a value to change it to? That's really what I'm
after. I don't want to change it to whatever random value Oracle picks.
I want to reset it to it's original value.
Mark D Powell
The DBID should be unique in your shop. Why do you want it to be the
same as the primary database from which the copy was made?
-- Mark D Powell --
Chuck
Not the same as the source of the copy, but I want the destination of
the copy to have the same DBID after the duplication as it had before.
joel garry
That is a strange want, as you are saying you want to restore backups
that cannot apply. If you are duping from another db, why do you
think you should be able to use backups from before the dupe?
Recovery is based on transactions. The transactional chain is broken
as far as those backups are concerned.
jg
--
@home.com is bogus.
What's in your database? http://www.signonsandiego.com/news/nation/20080708-1544-medicare-deaddoctors.html
Chuck
>> Not the same as the source of the copy, but I want the destination of
>> the copy to have the same DBID after the duplication as it had before.
>
> That is a strange want, as you are saying you want to restore backups
> that cannot apply. If you are duping from another db, why do you
> think you should be able to use backups from before the dupe?
> Recovery is based on transactions. The transactional chain is broken
> as far as those backups are concerned.
And why can't I apply them? Anything in the rman catalog can be restored
even after a dupe. I'd just be restoring to a point in time before the
dupe. As stated in an earlier posting in this thread, this is a TEST
database and only gets cold backups. No recovery would be necessary.
In my specific case I have no intentions of ever restoring the old
backups. I just want to delete them with a "delete obsolete" command.
But since duplication changes the dbid, "delete obsolete" will not work
on those pre-duplication backups and they sit forever in both the rman
catalog and the MML.
joel garry
I'm wondering if this may answer your delete question, at least for
the catalog: http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcatdb002.htm#sthref969
Also see unregister:
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcatdb002.htm#sthref958
And a gentle reminder, it is often better to state what you are aiming
for, as well as what you have tried, then just ask how to do something
specific which may or may not be what you really want.
jg
--
@home.com is bogus.
Mark D Powell
> On Jul 10, 11:09 am, Chuck <chuckh1958_nos...@gmail.com> wrote:
>
>
>
>
>
> > joel garry wrote:
> > >> Not the same as the source of the copy, but I want the destination of
> > >> the copy to have the same DBID after the duplication as it had before.
>
> > > That is a strange want, as you are saying you want to restore backups
> > > that cannot apply. If you are duping from another db, why do you
> > > think you should be able to use backups from before the dupe?
> > > Recovery is based on transactions. The transactional chain is broken
> > > as far as those backups are concerned.
>
> > And why can't I apply them? Anything in the rman catalog can be restored
> > even after a dupe. I'd just be restoring to a point in time before the
> > dupe. As stated in an earlier posting in this thread, this is a TEST
> > database and only gets cold backups. No recovery would be necessary.
>
> > In my specific case I have no intentions of ever restoring the old
> > backups. I just want to delete them with a "delete obsolete" command.
> > But since duplication changes the dbid, "delete obsolete" will not work
> > on those pre-duplication backups and they sit forever in both the rman
> > catalog and the MML.
>
> I'm wondering if this may answer your delete question, at least for
> Also see unregister:http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcat...
>
> And a gentle reminder, it is often better to state what you are aiming
> for, as well as what you have tried, then just ask how to do something
> specific which may or may not be what you really want.
>
> jg
> --
>
> - Show quoted text -
I agree with Joel. His recommendation would be the better approach.
Chuck
>
> And a gentle reminder, it is often better to state what you are aiming
> for,
I thought I did in the OP.
Chuck
>
> I'm wondering if this may answer your delete question, at least for
> the catalog: http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcatdb002.htm#sthref969
> Also see unregister:
> http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcatdb002.htm#sthref958
>
I may be wrong but I don't believe after a duplication the auxiliary
db's backups done under the old dbid have a status of "DELETED". I think
they are still "AVAILABLE". At least that's what "list backup" reports
(after jumping through a few hoops to get it to even be able to list
them). And I want them hanging around for a while in case I have to fall
back to one of them. After all isn't that point of taking a backup in
the first place?
Also I don't want to unregister the database. That would delete all of
the backups for the old dbid. I want them kept until they are older than
30 days (which is my configured retention period).
These are exactly the reasons why I *want* to be able to reset the dbid
to the original one. To simplify administrations of backups on a test
db, in noarchivelog mode, who's backups will always be done offline, and
where I want to use a simple "delete obsolete" to manage the old backups.
Mark D Powell
> joel garry wrote:
>
> > I'm wondering if this may answer your delete question, at least for
> > Also see unregister:
> >http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcat...
>
> db's backups done under the old dbid have a status of "DELETED". I think
> they are still "AVAILABLE". At least that's what "list backup" reports
> (after jumping through a few hoops to get it to even be able to list
> them). And I want them hanging around for a while in case I have to fall
> back to one of them. After all isn't that point of taking a backup in
> the first place?
>
> Also I don't want to unregister the database. That would delete all of
> the backups for the old dbid. I want them kept until they are older than
> 30 days (which is my configured retention period).
>
> These are exactly the reasons why I *want* to be able to reset the dbid
> to the original one. To simplify administrations of backups on a test
> db, in noarchivelog mode, who's backups will always be done offline, and
> where I want to use a simple "delete obsolete" to manage the old backups.
If these are test/development databases that are regularly overlaid
from another source why are you backing them up with rman to begin
with. Wouldn't an export taken in the middle of the night provide the
necessary backup that you could keep for N time? Using rman to do the
backup in this case seems like an unnecessary complication.
Charles Hooper
I wonder if a simple change in the TNSNAMES.ORA is sufficient to
accomplish what needs to be done? Assume that TNSNAMES.ORA contains
the following to allow a client to connect to the production database
instance:
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = PROD-SERVER)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
)
)
Also, assume that you need a TNSNAMES.ORA entry to connect to the test
database instance on the test server that looks like this:
TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TEST-SERVER)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST)
)
)
On the client computer that should only connect to the test database
instance when using the same connection string as would be used on
another client when connecting to the production server, the
TNSNAMES.ORA entry might look like this:
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TEST-SERVER)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST)
)
)
In the above, when the client requests to connect to "PROD", it
actually connects to a database instance named "TEST" on a different
server. I recently used the above approach to test a newer version of
an ERP package, and a number of custom developed applications with
hard coded connection strings targeting the data of the ERP system. A
similar approach may work for your needs.
I believe that DBID has a special meaning, which is causing some
concern from people responding in this thread, when you state you want
two databases to have the same DBID.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
joel garry
Now that we have a better idea of what Chuck wants to do, I'd say his
requirements are reasonable. Some apps have very special needs in
order to change schema names or work from an imp (personally, I wish
there was an alter schema command, because I really don't like having
the same schema name in test and production for the app I work on, so
it makes it necessary for me to have a complicated and slower process
to make test or dev db's). He wants to be able to dup a db, then be
able to restore previous to the dupe. RMAN ought to be able to make
that easy, but trying to figure out how is making my brain hurt, and
I'm not sure what obvious thing I've overlooked, and I don't have time
to test.
Charles' post is the only one he has made that I've seen, where my
reaction was "Egad! Don't tell people to do that!" Different
experiences make different fears, I guess. I've seen bigtime screwups
when people start getting cute with misdirecting tnsnames entries.
I've seen even bigger screwups where they can't get it right in the
first place. I've even been feeling a little guilty the past few days
because I have an update I wrote where I named the script based on the
particular location it is updating, and then added another location to
it (kind of the downside of "if you want to get something done fast,
give it to a busy person").
jg
--
@home.com is bogus.
"When one engine fails on a twin-engine airplane, you always have
enough power left to get you to the scene of the crash."
Chuck
They are too big for export to finish in a timely manner and there's
plenty of development time and work that transpires between the
refreshes so I dont want to risk losing it. Hence I do backups after
refreshing, while work is going on. Eventually that work will be
extracted and migrated to production. Then it will be time for another
refresh (eventually). There may be 20+ backups taken before then though.
Charles Hooper
> Charles' post is the only one he has made that I've seen, where my
> reaction was "Egad! Don't tell people to do that!" Different
> experiences make different fears, I guess. I've seen bigtime screwups
> when people start getting cute with misdirecting tnsnames entries.
> I've seen even bigger screwups where they can't get it right in the
> first place. I've even been feeling a little guilty the past few days
> because I have an update I wrote where I named the script based on the
> particular location it is updating, and then added another location to
> it (kind of the downside of "if you want to get something done fast,
> give it to a busy person").
>
> jg
> --
Joel,
Based on your response, and the others that appeared after my post in
this thread, I may have misunderstood what the OP is attempting to
accomplish. I assumed the following:
Server 1 (running production)
Client 1 (running production)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Server 2 (running some sort of a copy of the production database)
Client 2 (used for connecting to the copy of the production database
only)
Testing was needing to be performed by Client 2 against Server 2
without modifying the scripts or programs used by Client 1 against
Server 1. There is a potential for problems with the above, just as
there would be with someone accidentally forgetting to change from the
default production connection string to the non-default test
connection string before dropping a couple tables. The above change
limits the chances of someone asking - "oh, was I supposed to specify
a different database name" an hour after a conversion script starts
taking the "test" database from version X to version Y.
Joel, I trust your experience. As you find the approach that I
suggested in this thread dangerous, I suggest that it be considered
that way, and not implemented. Thanks for raising the red flag.
Chuck
Actually my original question is much simpler. Is it possible to change
a dbid and specify the new dbid rather than have nid pick one for you?
I'm gathering that there's no way to do it.
DANY
I'm not an expert but I think that isn't possible to specify a new
dbid.
I don't read all the posts but if you want to remain with the same
dbid, you can manually clone the database, instead of using rman
duplicate command.
Metalink notes :
Note:360962.1 - Manual Completion of a Failed RMAN Duplicate
Note:562556.1 - How to Manually Clone a Database to Another Node
Dany
Chuck
>
> I'm not an expert but I think that isn't possible to specify a new
> dbid.
> I don't read all the posts but if you want to remain with the same
> dbid, you can manually clone the database, instead of using rman
> duplicate command.
> Metalink notes :
> Note:360962.1 - Manual Completion of a Failed RMAN Duplicate
> Note:562556.1 - How to Manually Clone a Database to Another Node
>
> Dany
That would clone the production DB's dbid. I want to reuse the test db's
original dbid.
The only other thing that might work would be to use transportable
tablespaces but that requires then to be placed into read-only mode for
a while on production and I cant do that.
joel garry
Clone DB and use the transportables from that? Sounds weird at first,
but if you are fighting too hard against RMAN, it might make sense to
have another db temporarily. Depending on your space and other things
like that too, of course. Apologies if I've once again lost track of
what you are doing, but it seems you might need to have different
backup/restore requirements for the data and the work, and the usual
case includes modifying data definitions for the new work.
jg
--
@home.com is bogus.
http://online.barrons.com/article/SB121097976028700003.html?page=sp