NID Utility in Oracle
nid is the utility of oracle for changing the DBID and DB name but the datafile name is not changed.
These topics and the primary conditions of each topic
-
Changing the DBID and Database Name
After finishing this task, You have to change DB_NAME in the initialization parameter and open the database with resetlogs.
-
Changing Only the Database ID
this topic is very serious, every time, after you change dbid, You must open the database with resetlogs.
-
Changing Only the Database Name
for change the only database name, You don’t have to open the database without resetlogs, but don’t forget to change db_name in the initialization parameter.
Before the Task, You have to prepare
|
Changing The DBID and Database Name
Command
| [oracle@labdbt3 ~]$ nid TARGET=SYS DBNAME=DB1121G logfile=dbid_dbname.log |
for the Example
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 DB11G
labdbt3.localdomain
11.2.0.1.0 02-MAR-17 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
1* select DBID,name,CREATED,log_mode,OPEN_RESETLOGS,VERSION_TIME,OPEN_MODE,DATABASE_ROLE from v$database
SQL> /
DBID NAME CREATED LOG_MODE OPEN_RESETL VERSION_T OPEN_MODE DATABASE_ROLE
---------- --------- --------- ------------ ----------- --------- -------------------- ----------------
468941731 DB11G 03-FEB-17 NOARCHIVELOG NOT ALLOWED 03-FEB-17 MOUNTED PRIMARY
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/DB11G/users01.dbf
/home/oracle/oradata/DB11G/undotbs01.dbf
/home/oracle/oradata/DB11G/sysaux01.dbf
/home/oracle/oradata/DB11G/system01.dbf
/home/oracle/oradata/DB11G/example01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/home/oracle/oradata/DB11G/redo03_1.log
/home/oracle/oradata/DB11G/redo03_2.log
/home/oracle/oradata/DB11G/redo02_1.log
/home/oracle/oradata/DB11G/redo02_2.log
/home/oracle/oradata/DB11G/redo01_1.log
/home/oracle/oradata/DB11G/redo01_2.log
1* select * from v$controlfile
SQL> /
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------------------------------- --- ---------- --------------
/home/oracle/oradata/DB11G/control01.ctl NO 16384 594
/home/oracle/flash_recovery_area/DB11G/control02.ctl NO 16384
SQL> show parameter password
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3273641984 bytes
Fixed Size 2217792 bytes
Variable Size 1795164352 bytes
Database Buffers 1459617792 bytes
Redo Buffers 16642048 bytes
Database mounted.
[oracle@labdbt3 ~]$ nid TARGET=SYS DBNAME=DB1121G logfile=dbid_dbname.log
DBNEWID: Release 11.2.0.1.0 - Production on Sat Mar 2 20:41:35 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database DB11G (DBID=471541096)
Connected to server version 11.2.0
Control Files in database:
/home/oracle/oradata/DB11G/control01.ctl
/home/oracle/flash_recovery_area/DB11G/control02.ctl
Change database ID and database name DB11G to DB1121G? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 471541096 to 2401641283
Changing database name from DB11G to DB1121G
Control File /home/oracle/oradata/DB11G/control01.ctl - modified
Control File /home/oracle/flash_recovery_area/DB11G/control02.ctl - modified
Datafile /home/oracle/oradata/DB11G/system01.db - dbid changed, wrote new name
Datafile /home/oracle/oradata/DB11G/sysaux01.db - dbid changed, wrote new name
Datafile /home/oracle/oradata/DB11G/undotbs01.db - dbid changed, wrote new name
Datafile /home/oracle/oradata/DB11G/users01.db - dbid changed, wrote new name
Datafile /home/oracle/oradata/DB11G/example01.db - dbid changed, wrote new name
Datafile /home/oracle/oradata/DB11G/temp01.db - dbid changed, wrote new name
Control File /home/oracle/oradata/DB11G/control01.ctl - dbid changed, wrote new name
Control File /home/oracle/flash_recovery_area/DB11G/control02.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to DB1121G.
Modify parameter file and generate a new password file before restarting.
Database ID for database DB1121G changed to 2401641283.
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.
[oracle@labdbt3 ~]$
[oracle@labdbt3 ~]$
[oracle@labdbt3 ~]$
[oracle@labdbt3 ~]$
[oracle@labdbt3 ~]$
[oracle@labdbt3 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 2 20:42:46 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3273641984 bytes
Fixed Size 2217792 bytes
Variable Size 1795164352 bytes
Database Buffers 1459617792 bytes
Redo Buffers 16642048 bytes
ORA-01103: database name 'DB1121G' in control file is not 'DB11G'
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 DB11G
labdbt3.localdomain
11.2.0.1.0 02-MAR-17 STARTED NO 0 STOPPED
ALLOWED NO ACTIVE UNKNOWN NORMAL NO
SQL>
SQL>
SQL>
SQL>
SQL> ALTER SYSTEM SET DB_NAME=DB1121G scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3273641984 bytes
Fixed Size 2217792 bytes
Variable Size 1795164352 bytes
Database Buffers 1459617792 bytes
Redo Buffers 16642048 bytes
Database mounted.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL> select DBID,name,CREATED,log_mode,OPEN_RESETLOGS,VERSION_TIME,OPEN_MODE,DATABASE_ROLE from v$database;
DBID NAME CREATED LOG_MODE OPEN_RESETL VERSION_T OPEN_MODE DATABASE_ROLE
---------- --------- --------- ------------ ----------- --------- -------------------- ----------------
2401641283 DB1121G 03-FEB-17 NOARCHIVELOG NOT ALLOWED 03-FEB-17 READ WRITE PRIMARY
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 DB11G labdbt3.localdomain 11.2.0.1.0 02-MAR-17 OPEN NO 1 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
SQL>
Changing Only the Database ID
Command
| [oracle@labdbt3 ~]$ nid TARGET=SYS/password logfile=dbid.log |
Changing Only the Database Name
Command
| [oracle@labdbt3 ~]$ nid TARGET=SYS/password DBNAME=DB1121G logfile=dbname.log |
How to renaming and relocating datafiles