Change Oracle DB Name ,DBID and Both

-

Change Oracle DB Name ,DBID and Both


Oracle DBA changing database name ,DBID or both are common activity which are needed to be performed regularly.  With the introduction on the NID utility this has become easy to make such changes.In this blog , We will show you how to change :
1. DB Name and ID 
2. DB Name
3. DBID 

using NID utility.

Change DB Name and DBID:


1) Take Full backup of the database and mount database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  251658240 bytes
Fixed Size                  2236368 bytes
Variable Size             161210416 bytes
Database Buffers           83886080 bytes
Redo Buffers                4325376 bytes
Database mounted.
SQL> !
$ . oraenv
ORACLE_SID = [testdb1] ?
$ exit
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 testdb1
oraclehost01
10.2.0.4.0        07-JUN-12 MOUNTED      NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO

SQL> select dbid ,name from v$database;
      DBID NAME
---------- ---------
1049010805 testdb1
SQL> !

2) Use NID to change the Name and DBID both.

$ nid target="/ as sysdba" dbname=testdb2
DBNEWID: Release  - Production on Thu Jun 7 12:12:36 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Password:
Connected to database testdb1 (DBID=1049010805)
Connected to server version 
Control Files in database:
    /u03/oradata/testdb1/testdb1_control_01.ctl
    /u04/oradata/testdb1/testdb1_control_02.ctl
    /u02/oradata/testdb1/testdb1_control_03.ctl
Change database ID and database name testdb1 to testdb2? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1049010805 to 1099025088
Changing database name from testdb1 to testdb2
    Control File /u03/oradata/testdb1/testdb1_control_01.ctl - modified
    Control File /u04/oradata/testdb1/testdb1_control_02.ctl - modified
    Control File /u02/oradata/testdb1/testdb1_control_03.ctl - modified
    Datafile /u03/oradata/testdb1/testdb1_system_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/testdb1/testdb1_undotbs_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/testdb1/testdb1_sysaux_01.dbf - dbid changed, wrote new name
    Datafile /u03/oradata/testdb1/testdb1_users_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/testdb1/testdb1_tools_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/testdb1/testdb1_ilogdwp_01.dbf - dbid changed, wrote new name
    Datafile /u02/oradata/testdb1/testdb1_temp_01.dbf - dbid changed, wrote new name
    Control File /u03/oradata/testdb1/testdb1_control_01.ctl - dbid changed, wrote new name
    Control File /u04/oradata/testdb1/testdb1_control_02.ctl - dbid changed, wrote new name
    Control File /u02/oradata/testdb1/testdb1_control_03.ctl - dbid changed, wrote new name
    Instance shut down
Database name changed to testdb2.
Modify parameter file and generate a new password file before restarting.
Database ID for database testdb2 changed to 1099025088.
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 name and ID.
DBNEWID - Completed succesfully.

3) Change the DB name in parameter file and Start with open Resetlogs .

SQL> startup nomount pfile='/u01/app/oracle/admin/testdb2/pfile/inittestdb2.ora'
ORACLE instance started.
Total System Global Area  251658240 bytes
Fixed Size                  2236368 bytes
Variable Size              98295856 bytes
Database Buffers          146800640 bytes
Redo Buffers                4325376 bytes
SQL> alter database mount;
Database altered.


SQL> alter database open resetlogs;
Database altered.

Change DB Name Only


1) Take Full backup of the database and mount database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  251658240 bytes
Fixed Size                  2236368 bytes
Variable Size             161210416 bytes
Database Buffers           83886080 bytes
Redo Buffers                4325376 bytes
Database mounted.

2) Use NID to change DB Name only (With Setname=YES) Parameter.

$>nid TARGET="/ as sysdba" DBNAME=testdb2 SETNAME=YES
DBNEWID: Release 11.2.0.2.0 - Production on Mon Jun 16 09:30:23 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to database testdb1 (DBID=515887169)
Connected to server version
Control Files in database:
    /u02/oradata/testdb1/testdb1_control_01.ctl
    /u03/oradata/testdb1/testdb1_control_02.ctl
    /u04/oradata/testdb1/testdb1_control_03.ctl
Change database name of database testdb1 to testdb2? (Y/[N]) => Y
Proceeding with operation
Changing database name from testdb1 to testdb2
    Control File /u02/oradata/testdb1/testdb1_control_01.ctl - modified
    Control File /u03/oradata/testdb1/testdb1_control_02.ctl - modified
    Control File /u04/oradata/testdb1/testdb1_control_03.ctl - modified
    Datafile /u04/oradata/testdb1/testdb1_system_01.db - wrote new name
    Datafile /u02/oradata/testdb1/testdb1_undotbs_01.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_sysaux_01.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_users_01.db - wrote new name
    Datafile /u02/oradata/testdb1/testdb1_tools_01.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_BUB_DATA_01.db - wrote new name
    Datafile /u03/oradata/testdb1/testdb1_BUB_INDX_01.db - wrote new name
    Datafile /u03/oradata/testdb1/testdb1_DFL_DATA_01.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_DFL_INDX_01.db - wrote new name
    Datafile /u02/oradata/testdb1/testdb1_WIS_DATA_01.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_WIS_INDX_01.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_undotbs_02.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_DFL_DATA_02.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_DFL_DATA_03.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_DFL_DATA_04.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_DFL_DATA_05.db - wrote new name
    Datafile /u03/oradata/testdb1/testdb1_dfl_data_u03_autoadd_01.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_dfl_data_u02_autoadd_01.db - wrote new name
    Datafile /u03/oradata/testdb1/testdb1_dfl_data_u03_autoadd_02.db - wrote new name
    Datafile /u02/oradata/testdb1/testdb1_dfl_data_u04_autoadd_01.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_dfl_data_u02_autoadd_02.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_dfl_data_u02_autoadd_03.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_dfl_data_u02_autoadd_04.db - wrote new name
    Datafile /u03/oradata/testdb1/testdb1_DFL_DATA_06.db - wrote new name
    Datafile /u02/oradata/testdb1/testdb1_bub_data_u04_autoadd_02.db - wrote new name
    Datafile /u02/oradata/testdb1/testdb1_DFL_DATA_07.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_sysaux_u02_autoadd_05.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_SOX_DATA01.db - wrote new name
    Datafile /u03/oradata/testdb1/testdb1_DFL_DATA_08.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_DFL_DATA_09.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_dfl_data_u02_autoadd_06.db - wrote new name
    Datafile /u02/oradata/testdb1/testdb1_dfl_indx_u04_autoadd_03.db - wrote new name
    Datafile /u02/oradata/testdb1/testdb1_DFL_INDX_02.db - wrote new name
    Datafile /u02/oradata/testdb1/testdb1_bub_data_u04_autoadd_03.db - wrote new name
    Datafile /u02/oradata/testdb1/testdb1_bub_data_u04_autoadd_04.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_DFL_INDX_02.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_DFL_INDX_04.db - wrote new name
    Datafile /u03/oradata/testdb1/testdb1_DFL_INDX_02.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_sysaux_u02_autoadd_06.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_temp_01.db - wrote new name
    Datafile /u03/oradata/testdb1/testdb1_temp_02.db - wrote new name
    Datafile /u04/oradata/testdb1/testdb1_temp_03.db - wrote new name
    Control File /u02/oradata/testdb1/testdb1_control_01.ctl - wrote new name
    Control File /u03/oradata/testdb1/testdb1_control_02.ctl - wrote new name
    Control File /u04/oradata/testdb1/testdb1_control_03.ctl - wrote new name
    Instance shut down
Database name changed to testdb1.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

3) Change the DB name in parameter file and Start with open Resetlogs .

SQL> startup nomount pfile='/u01/app/oracle/admin/testdb2/pfile/inittestdb2.ora'
ORACLE instance started.
Total System Global Area  251658240 bytes
Fixed Size                  2236368 bytes
Variable Size              98295856 bytes
Database Buffers          146800640 bytes
Redo Buffers                4325376 bytes
SQL> alter database mount;
Database altered.


SQL> alter database open resetlogs;
Database altered.

Change DBID Only


1) Take Full backup of the database and mount database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  251658240 bytes
Fixed Size                  2236368 bytes
Variable Size             161210416 bytes
Database Buffers           83886080 bytes
Redo Buffers                4325376 bytes
Database mounted.

2) Use NID to change DBID only(Without DBNAME argument )

$nid target="/ as sysdba"
DBNEWID: Release  - Production on Sat Jun 2 19:08:28 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to database testdb1 (DBID=4249066509)
Connected to server version 10.2.0
Control Files in database:
    /u02/oradata/testdb1/testdb1_control_01.ctl
    /u03/oradata/testdb1/testdb1_control_02.ctl
    /u04/oradata/testdb1/testdb1_control_03.ctl
Change database ID of database testdb1? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 4249066509 to 4268740908
    Control File /u02/oradata/testdb1/testdb1_control_01.ctl - modified
    Control File /u03/oradata/testdb1/testdb1_control_02.ctl - modified
    Control File /u04/oradata/testdb1/testdb1_control_03.ctl - modified
    Datafile /u02/oradata/testdb1/testdb1_system_01.dbf - dbid changed
    Datafile /u02/oradata/testdb1/testdb1_undotbs1_01.dbf - dbid changed
    Datafile /u03/oradata/testdb1/testdb1_users_01.dbf - dbid changed
    Datafile /u02/oradata/testdb1/testdb1_tools_01.dbf - dbid changed
    Datafile /u02/oradata/testdb1/testdb1_wwim_data_01.dbf - dbid changed
    Datafile /u03/oradata/testdb1/testdb1_wwim_indx_01.dbf - dbid changed
    Datafile /u02/oradata/testdb1/testdb1_wwim_data_02.dbf - dbid changed
    Datafile /u02/oradata/testdb1/testdb1_wwim_data_03.dbf - dbid changed
    Datafile /u02/oradata/testdb1/testdb1_wwim_data_04.dbf - dbid changed
    Datafile /u02/oradata/testdb1/testdb1_wwim_data_05.dbf - dbid changed
    Datafile /u03/oradata/testdb1/testdb1_wwim_indx_02.dbf - dbid changed
    Datafile /u03/oradata/testdb1/testdb1_wwim_indx_03.dbf - dbid changed
    Datafile /u03/oradata/testdb1/testdb1_wwim_indx_04.dbf - dbid changed
    Datafile /u02/oradata/testdb1/testdb1_WWIM_EUL_01.dbf - dbid changed
    Datafile /u03/oradata/testdb1/testdb1_wwim_data_06.dbf - dbid changed
    Datafile /u04/oradata/testdb1/testdb1_wwim_indx_05.dbf - dbid changed
    Datafile /u04/oradata/testdb1/testdb1_wwim_data_07.dbf - dbid changed
    Datafile /u04/oradata/testdb1/testdb1_wwim_data_08.dbf - dbid changed
    Datafile /u02/oradata/testdb1/testdb1_wwim_data_temp_01.dbf - dbid changed
    Datafile /u04/oradata/testdb1/testdb1_wwim_data_temp_02.dbf - dbid changed
    Datafile /u04/oradata/testdb1/testdb1_wwim_data_temp_03.dbf - dbid changed
    Datafile /u02/oradata/testdb1/testdb1_sysaux_01.dbf - dbid changed
    Datafile /u02/oradata/testdb1/testdb1_temp01_01.dbf - dbid changed
    Control File /u02/oradata/testdb1/testdb1_control_01.ctl - dbid changed
    Control File /u03/oradata/testdb1/testdb1_control_02.ctl - dbid changed
    Control File /u04/oradata/testdb1/testdb1_control_03.ctl - dbid changed
    Instance shut down
Database ID for database testdb1 changed to 4268740908.
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.

3) Change the DB name in parameter file and Start with open Resetlogs .
$sqlplus "/as sysdba"
SQL*Plus: Release - Production on Sat Jun 2 19:08:56 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size                  2040920 bytes
Variable Size             956308392 bytes
Database Buffers          603979776 bytes
Redo Buffers               14729216 bytes
Database mounted.
SQL> alter database open RESETLOGS;
Database altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size                  2040920 bytes
Variable Size             956308392 bytes
Database Buffers          603979776 bytes
Redo Buffers               14729216 bytes
Database mounted.
Database opened.
SQL> set lines 132
SQL> select dbid from v$database;
      DBID
4268740908


==========================================================
Please check our other blogs.



Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restore MySQL Database from mysqlbackup

-
Image
 Restore MySQL Database from mysqlbackup  In todays world, One of the most common activity is to perform database restore using old backup or performing PIT recovery for database.  In this blog , We will show you how to perform restore and recovery of the MySQL database using backup taken via mysqlbackup. In order to restore Mysql database from MySQL backup , We will follow below steps: 1) Shut down MySQL Database Server/Service Check the status of the mysql service using command : systemctl status mysql Shut down mysql service using command : systemctl stop mysql 2) Delete all existing file of MySQL Server Delete all files inside the server's data directory. Also delete all files inside the directories specified by the --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory options for restore, if the directories are different from the data directory. 3) Restore full Backup mysqlbackup  --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --datadir=/var/lib
Read more

Oracle 19c New Features

-
Oracle 19c New Features As Oracle Support have made 12.2 version of Oracle ,its has become important for us to upgrade our 12c database to 19c.And before upgrade any database to latest version , We need to know the latest new features of that particular version.  In the current blog , We are going to discuss about new features of the Oracle 19C database : 1)Automatic Indexing  This is one of the important Oracle database 19c New feature. Automatic Indexing is one of the great feature included in 19c . It automates index management tasks, such as creating, rebuilding, and dropping indexes in an Oracle Database based on changes in the application workload. This feature improves database performance by managing indexes automatically in an Oracle Database. Oracle has provided new package DBMS_AUTO_INDEX for this. This enables automatic indexing in a database, but creates any new auto indexes as invisible indexes, so that they cannot be used in SQL statements: EXEC DBMS_AUTO_INDEX.CONFIGURE
Read more

Install & Configure MySQL Router - MySQL InnoDB Cluster

-
Image
Install & Configure MySQL Router -  MySQL InnoDB Cluster MySQL Router is one of the important component of MySQL InnoDB Cluster as it's used by applications to connect to the MySQL DBs running on cluster.  In this blog, We will show How we can install and configure MySQL Router either on application server or on dedicated server for MySQL Router. Below are the step to install and configure MySQL Router: 1) Install MySQL Router on the Server(In our case , We are restoring MySQL Router on the ubuntu server). Run command as root user : apt-get install MySQL-router 2) Bootstrap or Configure MySQL Router mysqlrouter --bootstrap clusteradmin@mysqlhost01:3306 --user=mysqlrouter In Case of any issue in configuring MySQL Router ,We can use -force option of the bootstrap. mysqlrouter --bootstrap clusteradmin@n4ldbdev5030:3306 --directory=/home/myrouter --user=mysqlrouter --force 3) Test Connectivity via MySQL Router. mysqlsh --uri clusteradmin@localhost:6446 you can learn more about
Read more