How to rename an Oracle database ?
How to rename an Oracle database ?
Changing a Database Name Using NEWID ( nid TARGET=/ )
Database name change from vihaan to xugarb
-- Take database backup if possible
-- Check all the tablespace should be in read write .
=========================
IMP !!!
startup mount ..
shutdown immediate
SQL> startup mount
nid TARGET=/ DBNAME=xugarb SETNAME=YES
nid TARGET=/ DBNAME=xugarb SETNAME=YES
DBNEWID: Release 12.1.0.2.0 - Production on Tue Sep 28 00:58:22 2021
Copyright (c) 1982, 2015, Oracle and/or its affiliates. All rights reserved.
Connected to database vihaan (DBID=4212949112)
Connected to server version 12.1.0
Control Files in database:
/pumps/UGARB/control01.ctl
/pumps/UGARB/control02.ctl
The following datafiles are read-only:
/pumps/UGARB/tts01.db (2) <<<<<<<<<<<<<< change to read write !!!!!!!!!!!
These files must be writable by this utility.
Change database name of database vihaan to XUGARB? (Y/[N]) =>
Operation cancelled at user request
Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.
=================================================================================================================
Imp !!!!
SQL> ALTER TABLESPACE TTS READ WRITE;
Tablespace altered.
select TABLESPACE_NAME from dba_tablespaces where STATUS='READ ONLY';
no rows selected
select * from V$TABLESPACE ;
set linesize 500 pagesize 300
col FILE_NAME for a50
select * from (select to_char(current_scn) current_scn ,to_char(checkpoint_change#) checkpoint_change# , to_char(controlfile_change#) controlfile_change#,to_char(CONTROLFILE_SEQUENCE#) CONTROLFILE_SEQUENCE# from v$database),
(select HXFIL File_num,substr(HXFNM,1,70) File_name,FHTYP Type,HXERR Validity,FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence ,d.enabled
from x$kcvfh f, v$datafile d where f.hxfil =d.file#
and d.enabled = 'READ ONLY'
)
no rows selected
[oracle@rac01 dbs]$
nid TARGET=/ DBNAME=xugarb SETNAME=YES
DBNEWID: Release 12.1.0.2.0 - Production on Tue Sep 28 02:09:45 2021
Copyright (c) 1982, 2015, Oracle and/or its affiliates. All rights reserved.
Connected to database vihaan (DBID=4212949112)
Connected to server version 12.1.0
Control Files in database:
/pumps/UGARB/control01.ctl
/pumps/UGARB/control02.ctl
Change database name of database vihaan to XUGARB? (Y/[N]) => Y
Proceeding with operation
Changing database name from vihaan to XUGARB
Control File /pumps/UGARB/control01.ctl - modified
Control File /pumps/UGARB/control02.ctl - modified
Datafile /pumps/UGARB/system01.db - wrote new name
Datafile /pumps/UGARB/tts01.db - wrote new name
Datafile /pumps/UGARB/sysaux01.db - wrote new name
Datafile /pumps/UGARB/undotbs01.db - wrote new name
Datafile /pumps/UGARB/undotbs02.db - wrote new name
Datafile /pumps/UGARB/users01.db - wrote new name
Datafile /pumps/UGARB/users02.db - wrote new name
Datafile /pumps/UGARB/test_data.db - wrote new name
Datafile /pumps/UGARB/test01.db - wrote new name
Datafile /pumps/UGARB/DATAFILE/vihaan1.645.106449357 - wrote new name
Datafile /pumps/UGARB/temp01.db - wrote new name
Datafile /pumps/UGARB/temp02.db - wrote new name
Control File /pumps/UGARB/control01.ctl - wrote new name
Control File /pumps/UGARB/control02.ctl - wrote new name
Instance shut down
Database name changed to XUGARB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
Change in init file !!
initugrab.ora <<<< *.db_name='xugarb'
startup pfile=initugrab.ora ;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 3712904 bytes
Variable Size 1090521208 bytes
Database Buffers 1040187392 bytes
Redo Buffers 13062144 bytes
Database mounted.
Database opened.
====
set pages 300 lines 350
col open_mode for a10
col host_name for a10
col database_role for a15
col db_unique_name for a10
col controlfile_type for a10
col database_role for a17
col host_name for a35
col open_mode for a20
select name,db_unique_name,instance_name,host_name,open_mode,log_mode,database_status,database_role,switchover_status,protection_mode,protection_level,controlfile_type,status,logins,force_logging,to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') "db up time" from v$database,gv$instance;
NAME DB_UNIQUE_ INSTANCE_NAME HOST_NAME OPEN_MODE LOG_MODE DATABASE_STATUS DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL CONTROLFIL STATUS LOGINS FORCE_LOGGING db up time
--------- ---------- ---------------- ----------------------------------- -------------------- ------------ ----------------- ----------------- -------------------- -------------------- -------------------- ---------- ------------ ---------- --------------------------------------- -----------------------------
XUGARB ugarbs ugarb rac01. READ WRITE ARCHIVELOG ACTIVE PRIMARY NOT ALLOWED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE CURRENT OPEN ALLOWED NO 28-sep-2021 02:12:51
