[ORACLE] DB_NAME 변경

<NID 사용>

1. pfile 생성 SQL> create pfile from spfile; File created.   2. DB 중지 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.   3. DB mount (pfile 사용) SQL> startup mount pfile='$ORACLE_HOME/dbs/initORCL.ora';   ORACLE instance started. Total System Global Area 2405122048 bytes Fixed Size            2255592 bytes Variable Size          603981080 bytes Database Buffers     1778384896 bytes Redo Buffers           20500480 bytes Database mounted.   4. listener, tns 상태 확인 [oracle@centos7-:~]$lsnrctl start listener [oracle@centos7-:~]$tnsping ORCL   5. NID utility - dbname 변경 [oracle@centos7-:~]$nid target=sys/oracle@ORCL dbname=TEST logfile=/nidlog   DBNEWID: Release 11.2.0.4.0 - Production on Thu Nov 5 21:29:07 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to database ORCL (DBID=1583333315) Connected to server version 11.2.0 Control Files in database:     /oradata/ORCL/control01.ctl     /oradata/ORCL/control02.ctl Changing database ID from 1583333315 to 2353826467 Changing database name from ORCL to TEST     Control File /oradata/ORCL/control01.ctl - modified     Control File /oradata/ORCL/control02.ctl - modified     Datafile /oradata/ORCL/system01.db - dbid changed, wrote new name     Datafile /oradata/ORCL/sysaux01.db - dbid changed, wrote new name     Datafile /oradata/ORCL/undotbs01.db - dbid changed, wrote new name     Datafile /oradata/ORCL/users01.db - dbid changed, wrote new name     Datafile /oradata/ORCL/test.db - dbid changed, wrote new name     Datafile /oradata/ORCL/temp01.db - dbid changed, wrote new name     Control File /oradata/ORCL/control01.ctl - dbid changed, wrote new name     Control File /oradata/ORCL/control02.ctl - dbid changed, wrote new name     Instance shut down Database name changed to TEST. Modify parameter file and generate a new password file before restarting. Database ID for database TEST changed to 2353826467. 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.   6. pfile 이름, 내용 변경 [oracle@centos7-:~]$vi initORCL.ora *.db_name='TEST'   [oracle@centos7-:~]$cp initORCL.ora initTEST.ora   7. password 파일 생성 [oracle@centos7-:~]$orapwd file=orapwTEST password=oracle entries=5   8. 환경변수 수정 vi ~/.bash_profile export ORACLE_SID=TEST . ~/.bash_profile   9. DB mount (변경된 pfile) SQL> startup mount pfile='$ORACLE_HOME/dbs/initTEST.ora'; ORACLE instance started. Total System Global Area 2405122048 bytes Fixed Size            2255592 bytes Variable Size          603981080 bytes Database Buffers     1778384896 bytes Redo Buffers           20500480 bytes Database mounted.   10. DB open SQL> alter database open resetlogs; Database altered.   --DB name 조회 SQL> select name from v$database; SQL> select instance_name from v$instance;

<controlfile 사용>

 

1. trace file 생성 SQL> alter database backup controlfile to trace as '/backup/con.sql'; Database altered.   2. pfile 생성 SQL> create pfile from spfile; File created.   3. DB 중지 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.   4. pfile 이름, 내용 변경 [oracle@centos7-:~]$vi initORCL.ora *.control_files='/oradata/ORCL/controlab.ctl','/oradata/ORCL/controlcd.ctl' *.db_name='ABCD'   [oracle@centos7-:~]$cp initORCL.ora initABCD.ora   5. trace file 수정 [oracle@centos7-:~]$vi con.sql   STARTUP NOMOUNT CREATE CONTROLFILE SET DATABASE "ABCD" RESETLOGS  ARCHIVELOG     MAXLOGFILES 16     MAXLOGMEMBERS 3     MAXDATAFILES 100     MAXINSTANCES 8     MAXLOGHISTORY 292 LOGFILE   GROUP 1 '/oradata/ORCL/redo01.log'  SIZE 50M BLOCKSIZE 512,   GROUP 2 '/oradata/ORCL/redo02.log'  SIZE 50M BLOCKSIZE 512,   GROUP 3 '/oradata/ORCL/redo03.log'  SIZE 50M BLOCKSIZE 512 DATAFILE   '/oradata/ORCL/system01.dbf',   '/oradata/ORCL/sysaux01.dbf',   '/oradata/ORCL/undotbs01.dbf',   '/oradata/ORCL/users01.dbf',   '/oradata/ORCL/test.dbf' CHARACTER SET WE8MSWIN1252 ;   6. 환경변수 수정 vi ~/.bash_profile export ORACLE_SID=ABCD . ~/.bash_profile   7. script 실행 SQL> @con.sql ORACLE instance started. Total System Global Area 2405122048 bytes Fixed Size            2255592 bytes Variable Size          603981080 bytes Database Buffers     1778384896 bytes Redo Buffers           20500480 bytes Control file created.   8. recover 수행 SQL> recover database using backup controlfile until cancel; ORA-00279: change 905470 generated at 11/05/2020 23:56:03 needed for thread 1 ORA-00289: suggestion : /arch/1_96_1055552579.arc ORA-00280: change 905470 for thread 1 is in sequence #96   Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled.   9. DB open SQL> alter database open resetlogs; Database altered.   --DB name 조회 SQL> select name from v$database; SQL> select instance_name from v$instance;