Contents
Changing the DBID and Database Name
with DBNEWID utility
Environment
Oracle
Linux Server release 7.3, Oracle database 12.1.0.2
Modifying
Oracle Home
Check /etc/oratab:
1. Rename database in /etc/oratab (after recreating
control file if doing so)
2. Remove/comment out other databases
in /etc/oratab if needed.
Check tnsnames.ora:
1. Modify if necessary. It can contain
some Listener entries.
2. Create an entry for the new database
Listener:
·
Change
HOST in listener.ora
·
Startup
listener
Changing
the DBID and Database Name with DBNEWID utility
If you just
need to change DBID and DB name use nid, otherwise
consider recreating control file.
Changing
the DBID and Database Name with DBNEWID utility (nid):
https://docs.oracle.com/database/121/SUTIL/GUID-05B4733C-9EAA-438F-A2A4-4E84EB1E1E65.htm#SUTIL1544
sqlplus / as sysdba
shutdown immediate
startup mount
exit
nid
TARGET=SYS DBNAME=new_name
Re-creating
control files
If you need
to change more than just DB name, e.g. directory name, then it is easier to
recreate the control file.
sqlplus / as sysdba
alter database backup controlfile to trace;
Go to “trace” directory e.g.:
/opt/oracle/diag/rdbms/mdrt115/MDRT115/trace
Or find where the trace directory is:
select * from V$DIAG_INFO;
Copy the trace file to SQL file, e.g. recreate_cf.sql
Modify the SQL script:
1. Remove all the lines upto the statement “STARTUP NOMOUNT”
2. Change the database name
3. Rename directories for datafiles and redo files.
4. Add SET after REUSE
5. RESETLOGS NOARCHIVELOG
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE set
DATABASE "MDRP115" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/data001/oradata/MDRP115/redo01.log' SIZE 50M
BLOCKSIZE 512,
GROUP 2 '/data001/oradata/MDRP115/redo02.log' SIZE 50M
BLOCKSIZE 512,
GROUP 3 '/data001/oradata/MDRP115/redo03.log' SIZE 50M
BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/data001/oradata/MDRP115/system01.dbf',
'/data001/oradata/MDRP115/sysaux01.dbf',
'/data001/oradata/MDRP115/undotbs01.dbf',
'/data001/oradata/MDRP115/users01.dbf',
'/data001/oradata/MDRP115/XMETA001.dbf',
'/data001/oradata/MDRP115/XMETASR001.dbf',
'/data001/oradata/MDRP115/IAUSER001.dbf',
'/data001/oradata/MDRP115/DSODBSPACE01.dbf',
'/data001/oradata/MDRP115/QSSRDSPACE01.dbf',
'/data001/oradata/MDRP115/ESDBSPACE01.dbf'
CHARACTER SET AL32UTF8
;
· Shutdown the database with immediate option. Do not abort.
shutdown immediate
· create pfile from spfile;
· rename data file directories if needed (and mount points in /etc/fstab)
· rename the just created pfile
mv initMDRT115.ora initMDRP115.ora
· edit the new pfile initMDRP115.ora and change dbname and directory names:
.audit_file_dest
.control_files
.db_name
.dispatchers
· Make sure directory for parameter audit_file_dest exists.
· Modify /etc/oratab, create an entry for the new database.
· Set new env variables (oraenv)
· Create new spfile from pfile
create spfile from pfile;
· Run SQL script to re-create control file
@recreate_cf.sql
ORACLE instance started.
Total System Global Area 1493172224 bytes
Fixed Size 2924592 bytes
Variable Size 536874960 bytes
Database Buffers 939524096 bytes
Redo Buffers 13848576 bytes
Control file created.
· Open database
ALTER DATABASE OPEN RESETLOGS;
· Add TEMP
ALTER TABLESPACE TEMP ADD TEMPFILE '/data001/oradata/MDRP115/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
· Create password file
cd $OH/dbs
orapwd FILE=orapwMDRP115
Re-creating
control file does not change DBID!
Changing the DBID
sqlplus / as sysdba
shutdown immediate
startup mount
exit
nid TARGET=SYS
DBNEWID: Release 12.1.0.2.0 - Production on Thu Apr 27 13:57:30 2017
Copyright (c) 1982, 2015, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database MDRP115 (DBID=3410461342)
Connected to server version 12.1.0
Control Files in database:
/data001/oradata/MDRP115/control01.ctl
/data001/oradata/MDRP115/control02.ctl
Change database ID of database MDRP115? (Y/[N]) => y
Proceeding with operation
Changing database ID from 3410461342 to 356948765
Control File /data001/oradata/MDRP115/control01.ctl - modified
Control File /data001/oradata/MDRP115/control02.ctl - modified
Datafile /data001/oradata/MDRP115/system01.db - dbid changed
Datafile /data001/oradata/MDRP115/sysaux01.db - dbid changed
Datafile /data001/oradata/MDRP115/undotbs01.db - dbid changed
Datafile /data001/oradata/MDRP115/users01.db - dbid changed
Datafile /data001/oradata/MDRP115/XMETA001.db - dbid changed
Datafile /data001/oradata/MDRP115/XMETASR001.db - dbid changed
Datafile /data001/oradata/MDRP115/IAUSER001.db - dbid changed
Datafile /data001/oradata/MDRP115/DSODBSPACE01.db - dbid changed
Datafile /data001/oradata/MDRP115/QSSRDSPACE01.db - dbid changed
Datafile /data001/oradata/MDRP115/ESDBSPACE01.db - dbid changed
Datafile /data001/oradata/MDRP115/temp01.db - dbid changed
Control File /data001/oradata/MDRP115/control01.ctl - dbid changed
Control File /data001/oradata/MDRP115/control02.ctl - dbid changed
Instance shut down
Database ID for database MDRP115 changed to 356948765.
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.
sqlplus / as sysdba
startup mount
ALTER DATABASE OPEN RESETLOGS;