Oracle
Oracle admin - nid utility를 활용한 DB 이름 변경
'Tis Heaven
2020. 3. 20. 01:10
※ 테스트 장비에서 수행했던 스크립트
1. initVFPROD.ora파일의 컨트롤 파일을 /app/oracle/VFPROD/data1/cntrl/cntrlPROD.dbf 로 변경
| *.control_files='/app/oracle/VFPROD/data1/cntrl/cntrlPROD.dbf' |
2. DB_NAME이 PROD인 상태로 DB mount 기동
| oraVFPROD 114> export ORACLE_SID=PROD oraVFPROD 115> sqlplus "/as sysdba" SQL> startup mount; |
3. nid 유틸리티로 db_name을 변경 (PROD -> VFPROD)
| oraVFPROD 118> nid target=sys/***** as sysdba dbname=VFPROD setname=yes DBNEWID: Release 11.2.0.3.0 - Production on Mon May 4 13:20:06 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to database PROD (DBID=869326457) Connected to server version 11.2.0 Control Files in database: /app/oracle/VFPROD/origlogA/cntrl/cntrlVFPROD.dbf /app/oracle/VFPROD/origlogB/cntrl/cntrlVFPROD.dbf /app/oracle/VFPROD/data1/cntrl/cntrlVFPROD.dbf Change database name of database PROD to VFPROD? (Y/[N]) => Y Proceeding with operation Changing database name from PROD to VFPROD Control File /app/oracle/VFPROD/origlogA/cntrl/cntrlVFPROD.dbf - modified Control File /app/oracle/VFPROD/origlogB/cntrl/cntrlVFPROD.dbf - modified Control File /app/oracle/VFPROD/data1/cntrl/cntrlVFPROD.dbf - modified Datafile /app/oracle/VFPROD/data1/system_1/system.data - wrote new name Datafile /app/oracle/VFPROD/data1/sysaux_1/sysaux.data - wrote new name Datafile /app/oracle/VFPROD/data1/undo_1/undo.data - wrote new name Datafile /app/oracle/VFPROD/data2/sr3_1/sr3.data - wrote new name Datafile /app/oracle/VFPROD/data2/sr3_2/sr3.data - wrote new name Datafile /app/oracle/VFPROD/data2/sr3_3/sr3.data - wrote new name Datafile /app/oracle/VFPROD/data2/sr3_4/sr3.data - wrote new name Datafile /app/oracle/VFPROD/data3/sr3702_1/sr3702.data - wrote new name Datafile /app/oracle/VFPROD/data3/sr3702_2/sr3702.data - wrote new name Datafile /app/oracle/VFPROD/data3/sr3702_3/sr3702.data - wrote new name Datafile /app/oracle/VFPROD/data3/sr3702_4/sr3702.data - wrote new name Datafile /app/oracle/VFPROD/data4/sr3usr_1/sr3usr.data - wrote new name Datafile /app/oracle/VFPROD/data4/sr3usr_2/sr3usr.data - wrote new name Datafile /app/oracle/VFPROD/data4/sr3usr_3/sr3usr.data - wrote new name Datafile /app/oracle/VFPROD/data2/sr3_5/sr3.data - wrote new name Datafile /app/oracle/VFPROD/data4/sr3usr_4/sr3usr.data - wrote new name Datafile /app/oracle/VFPROD/data4/sr3usr_5/sr3usr.data - wrote new name Datafile /app/oracle/VFPROD/data3/sr3_6/sr3.data - wrote new name Datafile /app/oracle/VFPROD/data3/sr3_7/sr3.data - wrote new name Datafile /app/oracle/VFPROD/data3/sr3_8/sr3.data - wrote new name Datafile /app/oracle/VFPROD/data4/sr3usr_6/sr3usr.data - wrote new name Datafile /app/oracle/VFPROD/data1/sr3usr_7/sr3usr.data - wrote new name Datafile /app/oracle/VFPROD/data1/sr3_9/sr3.data - wrote new name Datafile /app/oracle/VFPROD/data1/sr3usr_8/sr3usr.data - wrote new name Datafile /app/oracle/VFPROD/data1/sr3usr_9/sr3usr.data - wrote new name Datafile /app/oracle/VFPROD/data1/sr3_10/sr3.data1 - wrote new name Datafile /app/oracle/VFPROD/data3/sr3_11/sr3.data1 - wrote new name Datafile /app/oracle/VFPROD/data3/sr3_12/sr3.data1 - wrote new name Datafile /app/oracle/VFPROD/data2/sr3usr_10/sr3usr.data1 - wrote new name Datafile /app/oracle/VFPROD/data2/sr3_13/sr3.data1 - wrote new name Control File /app/oracle/VFPROD/origlogA/cntrl/cntrlVFPROD.dbf - wrote new name Control File /app/oracle/VFPROD/origlogB/cntrl/cntrlVFPROD.dbf - wrote new name Control File /app/oracle/VFPROD/data1/cntrl/cntrlVFPROD.dbf - wrote new name Instance shut down Database name changed to VFPROD. Modify parameter file and generate a new password file before restarting. Succesfully changed database name. DBNEWID - Completed succesfully. |
4. controlfile trace
| SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/app/oracle/VFPROD/WORK/20170504/cntrl.trc'; |
5. controlfile trace 내의 file 경로를 PROD -> VFPROD로 변경
| vi /app/oracle/VFPROD/WORK/20170504/cntrl.trc :%s/PROD/VFPROD/g |
6. initVFPROD.ora파일의 컨트롤 파일을 /app/oracle/VFPROD/sapdata1/cntrl/cntrlPROD.dbf 로 변경
| *.control_files='/app/oracle/VFPROD/origlogA/cntrl/cntrlVFPROD.dbf', '/app/oracle/VFPROD/origlogB/cntrl/cntrlVFPROD.dbf','/app/oracle/VFPROD/sapdata1/cntrl/cntrlVFPROD.dbf' |
7. controlfile 재생성
| SQL>@cntrl.sql STARTUP NOMOUNT; CREATE CONTROLFILE REUSE DATABASE "VFPROD" RESETLOGS ARCHIVELOG MAXLOGFILES 255 MAXLOGMEMBERS 3 MAXDATAFILES 1000 MAXINSTANCES 50 MAXLOGHISTORY 2336 LOGFILE GROUP 1 ( '/app/oracle/VFPROD/origlogA/log_g11m1.dbf', '/app/oracle/VFPROD/mirrlogA/log_g11m2.dbf' ) SIZE 200M BLOCKSIZE 512, GROUP 2 ( '/app/oracle/VFPROD/origlogB/log_g12m1.dbf', '/app/oracle/VFPROD/mirrlogB/log_g12m2.dbf' ) SIZE 200M BLOCKSIZE 512, GROUP 3 ( '/app/oracle/VFPROD/origlogA/log_g13m1.dbf', '/app/oracle/VFPROD/mirrlogA/log_g13m2.dbf' ) SIZE 200M BLOCKSIZE 512, GROUP 4 ( '/app/oracle/VFPROD/origlogB/log_g14m1.dbf', '/app/oracle/VFPROD/mirrlogB/log_g14m2.dbf' ) SIZE 200M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/app/oracle/VFPROD/data1/system_1/system.data1', '/app/oracle/VFPROD/data1/sysaux_1/sysaux.data1', '/app/oracle/VFPROD/data1/undo_1/undo.data1', '/app/oracle/VFPROD/data2/sr3_1/sr3.data1', '/app/oracle/VFPROD/data2/sr3_2/sr3.data2', '/app/oracle/VFPROD/data2/sr3_3/sr3.data3', '/app/oracle/VFPROD/data2/sr3_4/sr3.data4', '/app/oracle/VFPROD/data3/sr3702_1/sr3702.data1', '/app/oracle/VFPROD/data3/sr3702_2/sr3702.data2', '/app/oracle/VFPROD/data3/sr3702_3/sr3702.data3', '/app/oracle/VFPROD/data3/sr3702_4/sr3702.data4', '/app/oracle/VFPROD/data4/sr3usr_1/sr3usr.data1', '/app/oracle/VFPROD/data4/sr3usr_2/sr3usr.data2', '/app/oracle/VFPROD/data4/sr3usr_3/sr3usr.data3', '/app/oracle/VFPROD/data2/sr3_5/sr3.data5', '/app/oracle/VFPROD/data4/sr3usr_4/sr3usr.data4', '/app/oracle/VFPROD/data4/sr3usr_5/sr3usr.data5', '/app/oracle/VFPROD/data3/sr3_6/sr3.data6', '/app/oracle/VFPROD/data3/sr3_7/sr3.data7', '/app/oracle/VFPROD/data3/sr3_8/sr3.data8', '/app/oracle/VFPROD/data4/sr3usr_6/sr3usr.data6', '/app/oracle/VFPROD/data1/sr3usr_7/sr3usr.data7', '/app/oracle/VFPROD/data1/sr3_9/sr3.data9', '/app/oracle/VFPROD/data1/sr3usr_8/sr3usr.data8', '/app/oracle/VFPROD/data1/sr3usr_9/sr3usr.data9', '/app/oracle/VFPROD/data1/sr3_10/sr3.data10', '/app/oracle/VFPROD/data3/sr3_11/sr3.data11', '/app/oracle/VFPROD/data3/sr3_12/sr3.data12', '/app/oracle/VFPROD/data2/sr3usr_10/sr3usr.data10', '/app/oracle/VFPROD/data2/sr3_13/sr3.data13' CHARACTER SET UTF8 ; |
8. initVFPROD.ora파일의 db_name을 *.db_name='VFPROD'로 변경
| *.db_name='VFPROD' |
9. cancel base로 recover
| SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; ORA-00289: suggestion : /app/oracle/VFPROD/oraarch/VFPRODarch1_14962_819887195.dbf ORA-00280: change 7159153918 for thread 1 is in sequence #14962 ORA-00278: log file '/app/oracle/VFPROD/oraarch/PRODarch1_14961_819887195.dbf' no longer needed for this recovery Specify log: {=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. |
10. REDOLOG 가 COPY되지 않아 RESETLOG로 OPEN
| SQL> alter database open resetlogs; |
11. 과다사이즈로 생성되어 있는 TEMP를 삭제하기 위한 TEMP 재생성 작업
| SQL> create temporary tablespace dhtemp tempfile '/app/oracle/VFPROD/data1/temp_1/dh.emp' size 100m; Tablespace created. SQL> alter database default temporary tablespace dhtemp; Database altered. SQL> drop tablespace PTEMP; Tablespace dropped. SQL> create temporary tablespace PTEMP tempfile '/app/oracle/VFPROD/data1/temp_1/temp.data1' size 10000m reuse; Tablespace created. SQL> alter database default temporary tablespace PTEMP; Database altered. SQL> drop tablespace dhtemp; Tablespace dropped. |
12. 메모리 정리를 위한 db 재기동
| SQL> shutdown immediate; SQL> startup; |
13. db monitoring
| tail -f /app/oracle/VFPROD/trace/diag/rdbms/PROD/VFPROD/trace/alert_VFPROD.log |