Re: Changing DBID
Date: Tue, 8 Jul 2008 11:40:19 -0400
Message-ID: <iPGdnUApa5P-F-7VnZ2dnUVZ_jadnZ2d@comcast.com>
"Chuck" <chuckh1958_nospam_at_gmail.com> wrote in message
news:RKLck.1120$bn3.583_at_trnddc07...
> 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
- Backup the database
- SHUTDOWN IMMEDIATE of the database
- STARTUP MOUNT
- Open one session and run NID with sysdba privileges % nid TARGET=SYS/password_at_test_db
- Shutdown IMMEDIATE of the database
- Set the DB_NAME initialization parameter in the initialization parameter file to the new database name
- Create a new password file
- Startup of the database with open resetlogsExample:
- 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 bytesDatabase 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 bytesDatabase mounted.
SQL>exit
5. execute NID
C:\>nid target=sys/oracle_at_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 changedControl 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 bytesDatabase mounted.
9. SQL> alter database open resetlogs;
Database altered.
- 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