오라클 db name 변경 방법 nid, controlfile 재생성

 

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 12.2.0.1

 

방법 : 오라클 db name 변경 방법 nid, controlfile 재생성

db name을 변경하는 방법에 대해 설명함(nid 이용, controlfile 재생성 이용)

 

 

nid 라는 유틸리티를 이용해서 db name 변경

db name AADB12 에서 BBDB12 로 변경하는 시나리오

작업전 백업

$ rman target /
 
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Feb 25 14:09:17 2019
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: AADB12 (DBID=4070592250)
 
RMAN> backup database;

 

pfile 생성

SQL> create pfile from spfile;
 
File created.

 

인스턴스 이름 확인

SQL> select instance_name from v$instance;
 
INSTANCE_NAME
----------------
AADB12

 

db 이름 확인

SQL> select name from v$database;
 
NAME
---------
AADB12

 

db 종료

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 

pfile을 이용해 mount로 db 기동

SQL> startup mount pfile='$ORACLE_HOME/dbs/initAADB12.ora'
ORACLE instance started.
 
Total System Global Area  805306368 bytes
Fixed Size            8625856 bytes
Variable Size          314573120 bytes
Database Buffers      478150656 bytes
Redo Buffers            3956736 bytes
Database mounted.

 

tnsnames.ora 파일에 연결정보 등록

$ vi $ORACLE_HOME/network/admin/tnsnames.ora
AADB12 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.50)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = AADB12)
    )
  )

 

tnsping 테스트

$ tnsping AADB12
 
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 25-FEB-2019 14:02:16
 
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
 
Used parameter files:
/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle12)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = AADB12)))
OK (0 msec)

정상

 

nid 로그용 폴더 생성

$ mkdir -p /home/oracle/nidlog

 

nid로 dbname 변경 실행

*19c 환경에서는 nid target=sys dbname=BBB12 만 입력해주면됨 tns나 logfile 옵션을 넣을경우 NID-00106 에러 발생함, 이후 sys 패스워드 입력하면됨

$ nid target=sys/oracle@AADB12 dbname=BBDB12 logfile=/home/oracle/nidlog
 
DBNEWID: Release 12.2.0.1.0 - Production on Mon Feb 25 14:02:43 2019
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
NID-00101: LOGFILE (/home/oracle/nidlog) not accessible
NID-00490: LOG being redirected to STDERR due to errors.
 
Connected to database AADB12 (DBID=4070592250)
 
Connected to server version 12.2.0
 
Control Files in database:
    /oracle/app/oracle/oradata/AADB12/control01.ctl
    /oracle/app/oracle/oradata/AADB12/control02.ctl
 
Changing database ID from 4070592250 to 2870152963
Changing database name from AADB12 to BBDB12
    Control File /oracle/app/oracle/oradata/AADB12/control01.ctl - modified
    Control File /oracle/app/oracle/oradata/AADB12/control02.ctl - modified
    Datafile /oracle/app/oracle/oradata/AADB12/system01.db - dbid changed, wrote new name
    Datafile /oracle/app/oracle/oradata/AADB12/sysaux01.db - dbid changed, wrote new name
    Datafile /oracle/app/oracle/oradata/AADB12/undotbs01.db - dbid changed, wrote new name
    Datafile /oracle/app/oracle/oradata/AADB12/users01.db - dbid changed, wrote new name
    Datafile /oracle/app/oracle/oradata/AADB12/temp01.db - dbid changed, wrote new name
    Control File /oracle/app/oracle/oradata/AADB12/control01.ctl - dbid changed, wrote new name
    Control File /oracle/app/oracle/oradata/AADB12/control02.ctl - dbid changed, wrote new name
    Instance shut down
 
Database name changed to BBDB12.
Modify parameter file and generate a new password file before restarting.
Database ID for database BBDB12 changed to 2870152963.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

 

pfile BBDB12이름으로 복제

$ cd $ORACLE_HOME/dbs
$ cp initAADB12.ora initBBDB12.ora

 

db_name을 AADB12에서 BBDB12로 변경 및 확인

$ cat initBBDB12.ora 
기존
AADB12.__data_transfer_cache_size=0
AADB12.__db_cache_size=557842432
AADB12.__inmemory_ext_roarea=0
AADB12.__inmemory_ext_rwarea=0
AADB12.__java_pool_size=4194304
AADB12.__large_pool_size=8388608
AADB12.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
AADB12.__pga_aggregate_target=268435456
AADB12.__sga_target=805306368
AADB12.__shared_io_pool_size=33554432
AADB12.__shared_pool_size=188743680
AADB12.__streams_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/AADB12/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/oracle/app/oracle/oradata/AADB12/control01.ctl','/oracle/app/oracle/oradata/AADB12/control02.ctl'
*.db_block_size=8192
*.db_name='AADB12'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=AADB12XDB)'
*.local_listener='LISTENER_AADB12'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=768m
*.undo_tablespace='UNDOTBS1'
 
변경
*.audit_file_dest='/oracle/app/oracle/admin/AADB12/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/oracle/app/oracle/oradata/AADB12/control01.ctl','/oracle/app/oracle/oradata/AADB12/control02.ctl'
*.db_block_size=8192
*.db_name='BBDB12'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=AADB12XDB)'
*.local_listener='LISTENER_AADB12'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=768m
*.undo_tablespace='UNDOTBS1'

*db_name 변경 외 필요시 AADB12로 되어있는 폴더명 BBDB12로 변경 및 폴더 생성, 컨트롤파일 해당경로로 복제

(명령어 vi 에디터 :%s/AADB12/BBDB12/g)

 

패스워드 파일 생성

$ orapwd file=orapwBBDB12 password='dhfkzmf123!' entries=5

 

.bash_profile의 ORACLE_SID변경

$ vi ~/.bash_profile
export ORACLE_SID=AADB12 --> 기존
export ORACLE_SID=BBDB12 --> 변경

 

새로운 pfile을 이용해 mount로 db 기동

SQL> startup mount pfile='$ORACLE_HOME/dbs/initBBDB12.ora'
ORACLE instance started.
 
Total System Global Area  805306368 bytes
Fixed Size            8625856 bytes
Variable Size          314573120 bytes
Database Buffers      478150656 bytes
Redo Buffers            3956736 bytes
Database mounted.

 

db OPEN 상태로 전환

SQL> alter database open resetlogs;
 
Database altered.

 

db 이름 재확인

SQL> select name from v$database;
 
NAME
---------
BBDB12

 

인스턴스 이름 재확인

SQL> select instance_name from v$instance;
 
INSTANCE_NAME
----------------
BBDB12

db 이름과 인스턴스 명이 바뀜

 

 

 

컨트롤 파일 재생성을 통한 db name 변경

db name BBDB12 에서 CCDB12 로 변경하는 시나리오

작업전 백업

$ rman target /
 
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Feb 25 14:09:17 2019
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: BBDB12 (DBID=2870152963)
 
RMAN> backup database;

 

컨트롤파일 생성문 백업

SQL> alter database backup controlfile to trace as '/home/oracle/re.sql';

 

db 종료

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 

pfile CCDB12이름으로 복제

$ cd $ORACLE_HOME/dbs
$ cp initBBDB12.ora initCCDB12.ora

 

initCCDB12.ora 파일 수정

$ vi initCCDB12.ora
*.db_name='BBDB12'
를
*.db_name='CCDB12'
로 변경 후
*.control_files='/oracle/app/oracle/oradata/AADB12/control01.ctl','/oracle/app/oracle/oradata/AADB12/control02.ctl'
를
*.control_files='/oracle/app/oracle/oradata/AADB12/controlCC01.ctl','/oracle/app/oracle/oradata/AADB12/controlCC02.ctl'
로 변경

 

확인

$ cat initCCDB12.ora 
AADB12.__data_transfer_cache_size=0
AADB12.__db_cache_size=557842432
AADB12.__inmemory_ext_roarea=0
AADB12.__inmemory_ext_rwarea=0
AADB12.__java_pool_size=4194304
AADB12.__large_pool_size=8388608
AADB12.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
AADB12.__pga_aggregate_target=268435456
AADB12.__sga_target=805306368
AADB12.__shared_io_pool_size=33554432
AADB12.__shared_pool_size=188743680
AADB12.__streams_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/AADB12/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/oracle/app/oracle/oradata/AADB12/controlCC01.ctl','/oracle/app/oracle/oradata/AADB12/controlCC02.ctl'
*.db_block_size=8192
*.db_name='CCDB12'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=AADB12XDB)'
*.local_listener='LISTENER_AADB12'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=768m
*.undo_tablespace='UNDOTBS1'

수정완료

 

백업한 컨트롤파일 생성문 re.sql 수정

$ vi re.sql
REUSE를 SET으로 DBNAME을 BBDB12에서 CCDB12로 변경 후 모든 주석 제거

 

확인

$ cat re.sql 
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CCDB12" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/app/oracle/oradata/AADB12/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/oracle/app/oracle/oradata/AADB12/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/oracle/app/oracle/oradata/AADB12/redo03.log'  SIZE 200M BLOCKSIZE 512
DATAFILE
  '/oracle/app/oracle/oradata/AADB12/system01.dbf',
  '/oracle/app/oracle/oradata/AADB12/sysaux01.dbf',
  '/oracle/app/oracle/oradata/AADB12/undotbs01.dbf',
  '/oracle/app/oracle/oradata/AADB12/users01.dbf'
CHARACTER SET KO16MSWIN949
;

 

pfile을 이용해 nomount로 db 기동

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initCCDB12.ora'
ORACLE instance started.
 
Total System Global Area  805306368 bytes
Fixed Size            8625856 bytes
Variable Size          314573120 bytes
Database Buffers      478150656 bytes
Redo Buffers            3956736 bytes

 

re.sql 실행

SQL> @re.sql
 
Control file created.

컨트롤 파일이 생성되며 mount 단계까지 올라감

 

recover 명령 수행

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1425451 generated at 02/25/2019 14:26:22 needed for thread 1
ORA-00289: suggestion : /oracle/app/oracle/product/12.2.0/dbhome_1/dbs/arch1_1_1001167652.dbf
ORA-00280: change 1425451 for thread 1 is in sequence #1
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel <- cancel 입력
Media recovery cancelled.

 

db OPEN 상태로 전환

SQL> alter database open resetlogs;
 
Database altered.

 

db 이름 재확인

SQL> select name from v$database;
 
NAME
---------
CCDB12

 

인스턴스 이름 재확인

SQL> select instance_name from v$instance;
 
INSTANCE_NAME
----------------
CCDB12

db 이름과 인스턴스 이름이 모두 변경됨

 

 

참조 : 

https://otsteam.tistory.com/283

https://docs.oracle.com/cd/B10501_01/server.920/a96652/ch14.htm

863800.1