17 | 10 | 2017
Latest Articles
Popular Articles

Backup & Recovery

NID : change DBID

User Rating:  / 0
PoorBest 

Change the DBID with NID

 

The DBID is an unique identifier for a database. You should only change the DBID of a database when really needed since a new DBID,

1. invalidates previously taken backups and backup information

2. invalidates previously generated archivelogs

3. resets the log sequence to 1

The DBID is used in RMAN'  s recovery catalog. Also you cannot recover archived redo logs when there is a DBID mismatch. Changing the database DB_NAME has less consequences with respect to database backup and recovery.

With the below procedure I show how easy it is to use NID ( but TAKE CARE )

 

 SQL> select dbid from v$database;


      DBID


3954204418

 

SQL> shutdown immediate;

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2050856 bytes
Variable Size             247465176 bytes
Database Buffers          817889280 bytes
Redo Buffers                6336512 bytes
Database mounted.
SQL> exit

oracle@myhost:/opt/oracle/backup $ nid target=sys/secret_password

DBNEWID: Release 10.2.0.3.0 - Production on Tue Mar 18 15:31:56 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to database my_db (DBID=3954204418)

Connected to server version 10.2.0

Control Files in database:
    +DG1/my_db/controlfile/current.509.649687193
    +DG2/my_db/controlfile/current.645.649687201

Change database ID of database my_db? (Y/N) =>

Proceeding with operation
Changing database ID from 3954204418 to 3976866604
    Control File +DG1/my_db/controlfile/current.509.649687193 - modified
    Control File +DG2/my_db/controlfile/current.645.649687201 - modified
    Datafile +DG1/my_db/datafile/system.646.649688265 - dbid changed
    Datafile +DG1/my_db/datafile/undotbs1.647.649688265 - dbid changed
    Datafile +DG1/my_db/datafile/sysaux.648.649688267 - dbid changed
    Datafile +DG1/my_db/datafile/users.649.649688267 - dbid changed
    Datafile +DG1/my_db/datafile/ts_my_db_admin_code.658.649688267 - dbid changed
    Datafile +DG1/my_db/datafile/ts_my_db_logging_data_0208.674.649688267 - dbid changed
    Datafile +DG1/my_db/datafile/ts_my_db_logging_data_0308.656.649688267 - dbid changed
    Datafile +DG1/my_db/datafile/ts_my_db_logging_data_0408.675.649688269 - dbid changed
    Datafile +DG1/my_db/datafile/ts_my_db_logging_data_0508.518.649688277 - dbid changed
    Datafile +DG1/my_db/datafile/ts_my_db_logging_data_0608.511.649688297 - dbid changed
    Datafile +DG1/my_db/datafile/ts_my_db_logging_indx.512.649688299 - dbid changed
    Datafile +DG1/my_db/tempfile/temp.643.649688777 - dbid changed
    Datafile +DG1/my_db/tempfile/temp01.642.649688777 - dbid changed
    Datafile +DG1/my_db/tempfile/temp02.641.649688779 - dbid changed
    Control File +DG1/my_db/controlfile/current.509.649687193 - dbid changed
    Control File +DG1/my_db/controlfile/current.645.649687201 - dbid changed
    Instance shut down

Database ID for database my_db changed to 3976866604.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database ID.
DBNEWID - Completed succesfully.


Oracle asks us t open the database with resetlogs, so let' s do that


oracle@myhost:/opt/oracle/backup $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Mar 18 15:33:16 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2050856 bytes
Variable Size             251659480 bytes
Database Buffers          813694976 bytes
Redo Buffers                6336512 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.



That' s it, changing the DBID goes fast and is easy, but again be aware what kind of impact it has with respect to your backup and recovery situation. A part from changing the DBID we can use nid to change the db_name only.