Is there a way to reset the DBID of a database to a specific value? For 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.
> Is there a way to reset the DBID of a database to a specific value? For > 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
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.
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
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.
On Jul 8, 4:59 pm, Chuck <chuckh1958_nos...@gmail.com> wrote:
> Tim Arnold wrote:
> > 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.
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 wrote: > On Jul 8, 4:59 pm, Chuck <chuckh1958_nos...@gmail.com> wrote: >> Tim Arnold wrote:
>>> 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.
> 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 --
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.
> Mark D Powell wrote: > > On Jul 8, 4:59 pm, Chuck <chuckh1958_nos...@gmail.com> wrote: > >> Tim Arnold wrote:
> >>> 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.
> > 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 --
> 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.
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.
> 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.
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.
> 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.
> 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.
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.
> 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.
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.
On Jul 8, 11:30 am, Chuck <chuckh1958_nos...@gmail.com> wrote:
> Is there a way to reset the DBID of a database to a specific value? For > 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
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.
> > 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.
> 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.
> -- Mark D Powell --
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."
Mark D Powell wrote: > On Jul 11, 2:13 pm, Chuck <chuckh1958_nos...@gmail.com> wrote: >> joel garry wrote:
>>> 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/rcmcat... >>> Also see unregister: >>> http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcat... >> 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.
> 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.
> -- Mark D Powell --
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.
On Jul 14, 1:13 pm, joel garry <joel-ga...@home.com> wrote:
> 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.
Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
Charles Hooper wrote: > On Jul 14, 1:13 pm, joel garry <joel-ga...@home.com> wrote: >> 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.
> Charles Hooper > IT Manager/Oracle DBA > K&M Machine-Fabricating, Inc.
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?
> Charles Hooper wrote: > > On Jul 14, 1:13 pm, joel garry <joel-ga...@home.com> wrote: > >> 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.
> > Charles Hooper > > IT Manager/Oracle DBA > > K&M Machine-Fabricating, Inc.
> 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.
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
> 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.
> > 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.
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.
