NID Utility

DBNEWNAME Utility는 DBNAME을 간편하게 바꾸어 주는 유틸리티입니다.

사용하기위해서는 mount 상태에서 진행해야 합니다.

 

DB 환경 : Oracle 11.2.0.1(64bit)

OS 환경 : Linux 7.5(64bit)

 

시나리오 : ysbae3의 이름을 가지고 있는 DB를 newysbae로 바꾸는 시나리오입니다.

 

현재 DB 상태 확인
SYS@ysbae3> select instance_name from v$instance;

INSTANCE_NAME
----------------
ysbae3

1 row selected.

Elapsed: 00:00:00.01
SYS@ysbae3> select name from v$database;

NAME
---------
YSBAE3

1 row selected.

 

파라미터파일 새로 생성
[oracle@ysbae ~]$ cd $ORACLE_HOME/dbs
[oracle@ysbae dbs]$ cp initysbae3.ora initnewysbae.ora

파라미터파일 수정

기존의 ysbae3이었던 이름을 모두 newysbae로 변경합니다.
 vi initnewysbae.ora
newysbae.__db_cache_size=603979776
newysbae.__java_pool_size=33554432
newysbae.__large_pool_size=16777216
newysbae.__oracle_base='/app/oracle'#ORACLE_BASE set from environment
newysbae.__pga_aggregate_target=671088640
newysbae.__sga_target=989855744
newysbae.__shared_io_pool_size=0
newysbae.__shared_pool_size=318767104
newysbae.__streams_pool_size=0
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/cloneDB/control01.ctl','/home/oracle/cloneDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='newysbae'
*.db_recovery_file_dest='/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ysbaeXDB)'
*.memory_target=655701504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

 

SID도 함께 변경 시 passwordfile 생성
[oracle@ysbae dbs]$ orapwd file=orapwdnewysbae password=oracle entries=5

[oracle@ysbae dbs]$ ls -al |grep newysbae
-rw-r--r--   1 oracle oinstall      854 Feb  4 15:01 initnewysbae.ora

-rw-r-----   1 oracle oinstall     2048 Feb  4 15:02 orapwdnewysbae

 

DB MOUNT로 변경하기

SYS@ysbae3> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ysbae3> startup mount
ORACLE instance started.

Total System Global Area  655556608 bytes
Fixed Size                  2216224 bytes
Variable Size             390074080 bytes
Database Buffers          260046848 bytes
Redo Buffers                3219456 bytes
Database mounted.

 

nid 수행
[oracle@ysbae dbs]$ nid target=sys/oracle DBNAME=newysbae

DBNEWID: Release 11.2.0.1.0 - Production on Tue Feb 4 15:03:29 2020

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database YSBAE3 (DBID=4232865478)

Connected to server version 11.2.0

Control Files in database:
    /home/oracle/cloneDB/control01.ctl
    /home/oracle/cloneDB/control02.ctl

Change database ID and database name YSBAE3 to NEWYSBAE? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 4232865478 to 3448484353
Changing database name from YSBAE3 to NEWYSBAE
    Control File /home/oracle/cloneDB/control01.ctl - modified
    Control File /home/oracle/cloneDB/control02.ctl - modified
    Datafile /home/oracle/cloneDB/system01.db - dbid changed, wrote new name
    Datafile /home/oracle/cloneDB/sysaux01.db - dbid changed, wrote new name
    Datafile /home/oracle/cloneDB/undotbs01.db - dbid changed, wrote new name
    Datafile /home/oracle/cloneDB/users01.db - dbid changed, wrote new name
    Datafile /home/oracle/cloneDB/example01.db - dbid changed, wrote new name
    Datafile /home/oracle/cloneDB/maxrepo01.db - dbid changed, wrote new name
    Datafile /home/oracle/cloneDB/test01.db - dbid changed, wrote new name
    Datafile /home/oracle/cloneDB/temp01.db - dbid changed, wrote new name
    Control File /home/oracle/cloneDB/control01.ctl - dbid changed, wrote new name
    Control File /home/oracle/cloneDB/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to NEWYSBAE.
Modify parameter file and generate a new password file before restarting.
Database ID for database NEWYSBAE changed to 3448484353.
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.

 

SID 변경 수행

[oracle@ysbae dbs]$ export ORACLE_SID=newysbae
[oracle@ysbae dbs]$ echo $ORACLE_SID
newysbae

 

DB Open

[oracle@ysbae dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 4 15:04:21 2020

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

Connected to an idle instance.

SYS@newysbae> startup mount pfile=/app/oracle/product/11.2.0.4/db_1/dbs/initnewysbae.ora
ORACLE instance started.

Total System Global Area  655556608 bytes
Fixed Size                  2216224 bytes
Variable Size             390074080 bytes
Database Buffers          260046848 bytes
Redo Buffers                3219456 bytes
Database mounted.

-resetlogs로 open해야 합니다.
SYS@newysbae> alter database open resetlogs;

Database altered.

Elapsed: 00:00:02.24

SYS@newysbae> select instance_name from v$instance;

INSTANCE_NAME
----------------
newysbae

1 row selected.

Elapsed: 00:00:00.01
SYS@newysbae> select name from v$database;

NAME
---------
NEWYSBAE

1 row selected.

 

spfile 재생성 및 OS의 .bash_profile 수정

SYS@newysbae> create spfile from pfile  ;

 File created.

 

끝