간단한 방법으로 SID 변경하기 (nid 사용)

고객사에서 아래의 작업이 매 주 일어납니다.

 

1. 운영 DB서버의 컨트롤/리두로그/데이터 파일 부분을 테스트 DB서버로 이미지카피를 합니다.

   (DB 엔진부분이나 설정파일은 복사하지 않습니다.)

2. 테스트 DB의 SID를 테스트에 맞게 변경해줍니다.

 

예전같으면 벌벌떨면서 진행했던 SID 변경 작업이지만,

10.x 이상의 버젼에서 nid라는 도구가 도입되면서 아주 간단한(실수를 하지 않는다면) 작업으로 변했습니다.

 

아래는 변경 절차입니다.

운영 DB서버의 SID는 PROD, 테스트 DB서버의 SID는 TEST라고 하겠습니다.

 

##########################################################################

이미지 카피가 정상적으로 되어 DB가 정상적으로 올라오는지 확인하는 부분

##########################################################################

 

1) 이미지카피가 완료된 후....

 

2) .profile에 설정되어 있는 SID를 PROD로 변경합니다.

 

3) . .profile로 변경된 사항을 적용합니다.

 

4) cp inittest.ora initprod.ora 명령으로 기 존재하는 파라메터 파일을 이용하여 초기화 파라메터 파일을 생성합니다.

 

5) 생성한 initprod.ora 파일을 열어서, controlfile의 위치를 실제 파일이 위치하는 곳으로 변경하고..

   db_name 파라메터를 prod로 변경하고..

   local listener 파라메터 설정 부분을 remark 합니다.

 

6) 패스워드 파일 사용중이라면 orapwd 명령으로 패스워드 파일을 생성합니다. 

 

7) sqlplus 에 로그인하고, startup 명령으로 DB를 살려서 정상적으로 올라오는지 확인합니다.

 

##########################################################################

SID을 바꾸고 DB가 정상적으로 올라오는지 확인하는 부분

##########################################################################

8) DB가 정상이라면 shutdown 명령으로 DB를 내립니다.

 

9) DB를 Mount 단계까지 올립니다.

 

10) 다른 창을 하나 띄워서 아래의 명령어로 SID를 변경합니다.

    nid target=sys/<패스워드> dbname=TEST setname=yes

    (nid가 실행되면 nid가 Mount되어 있는 DB를 shutdown 시킵니다.)

    <실제 수행 화면>

[oracle@sbiztdb1:prod:/sks1/oracle]nid target=sys/<패스워드> dbname=TEST setname=y

DBNEWID: Release 10.2.0.4.0 - Production on Sat Jun 13 22:53:29 2009

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

Connected to database PROD (DBID=3426403114)

Connected to server version 10.2.0

Control Files in database:
    /dev/hdvg21/rlvol_0500M0001
    /dev/hdvg22/rlvol_0500M0006
    /dev/hdvg23/rlvol_0500M0011

Change database name of database PROD to TEST? (Y/[N]) => Y

Proceeding with operation
Changing database name from PROD to TEST
    Control File /dev/hdvg21/rlvol_0500M0001 - modified
    Control File /dev/hdvg22/rlvol_0500M0006 - modified
    Control File /dev/hdvg23/rlvol_0500M0011 - modified
    Datafile /dev/hdvg21/rlvol_4000M0001 - wrote new name
    Datafile /dev/hdvg21/rlvol_8000M0002 - wrote new name
    Datafile /dev/hdvg22/rlvol_2000M0008 - wrote new name
    Datafile /dev/hdvg26/rlvol_4000M0042 - wrote new name
    Datafile /dev/hdvg22/rlvol_2000M0009 - wrote new name
    Datafile /dev/hdvg21/rlvol_8000M0003 - wrote new name
    Datafile /dev/hdvg21/rlvol_8000M0004 - wrote new name
    Datafile /dev/hdvg21/rlvol_8000M0005 - wrote new name
    Datafile /dev/hdvg21/rlvol_8000M0006 - wrote new name
    Datafile /dev/hdvg21/rlvol_8000M0007 - wrote new name
    Datafile /dev/hdvg22/rlvol_8000M0008 - wrote new name
    Datafile /dev/hdvg22/rlvol_8000M0009 - wrote new name
    Datafile /dev/hdvg22/rlvol_8000M0011 - wrote new name
    Datafile /dev/hdvg22/rlvol_8000M0012 - wrote new name
    Datafile /dev/hdvg22/rlvol_8000M0013 - wrote new name
    Datafile /dev/hdvg22/rlvol_8000M0014 - wrote new name
    Datafile /dev/hdvg23/rlvol_8000M0015 - wrote new name
    Datafile /dev/hdvg23/rlvol_8000M0016 - wrote new name
    Datafile /dev/hdvg23/rlvol_8000M0017 - wrote new name
    Datafile /dev/hdvg23/rlvol_8000M0018 - wrote new name
    Datafile /dev/hdvg23/rlvol_8000M0019 - wrote new name
    Datafile /dev/hdvg23/rlvol_8000M0020 - wrote new name
    Datafile /dev/hdvg23/rlvol_8000M0021 - wrote new name
    Datafile /dev/hdvg24/rlvol_8000M0022 - wrote new name
    Datafile /dev/hdvg24/rlvol_8000M0023 - wrote new name
    Datafile /dev/hdvg24/rlvol_8000M0024 - wrote new name
    Datafile /dev/hdvg24/rlvol_8000M0025 - wrote new name
    Datafile /dev/hdvg24/rlvol_8000M0026 - wrote new name
    Datafile /dev/hdvg24/rlvol_8000M0027 - wrote new name
    Datafile /dev/hdvg24/rlvol_8000M0028 - wrote new name
    Datafile /dev/hdvg25/rlvol_8000M0030 - wrote new name
    Datafile /dev/hdvg25/rlvol_8000M0031 - wrote new name
    Datafile /dev/hdvg25/rlvol_8000M0032 - wrote new name
    Datafile /dev/hdvg25/rlvol_8000M0034 - wrote new name
    Datafile /dev/hdvg25/rlvol_8000M0035 - wrote new name
    Datafile /dev/hdvg26/rlvol_8000M0036 - wrote new name
    Datafile /dev/hdvg26/rlvol_8000M0037 - wrote new name
    Datafile /dev/hdvg26/rlvol_8000M0038 - wrote new name
    Datafile /dev/hdvg21/rlvol_4000M0002 - wrote new name
    Datafile /dev/hdvg21/rlvol_4000M0003 - wrote new name
    Datafile /dev/hdvg21/rlvol_4000M0004 - wrote new name
    Datafile /dev/hdvg21/rlvol_4000M0005 - wrote new name
    Datafile /dev/hdvg21/rlvol_4000M0007 - wrote new name
    Datafile /dev/hdvg22/rlvol_4000M0008 - wrote new name
    Datafile /dev/hdvg22/rlvol_4000M0009 - wrote new name
    Datafile /dev/hdvg22/rlvol_4000M0010 - wrote new name
    Datafile /dev/hdvg22/rlvol_4000M0011 - wrote new name
    Datafile /dev/hdvg22/rlvol_4000M0013 - wrote new name
    Datafile /dev/hdvg23/rlvol_4000M0015 - wrote new name
    Datafile /dev/hdvg26/rlvol_8000M0040 - wrote new name
    Datafile /dev/hdvg26/rlvol_8000M0041 - wrote new name
    Datafile /dev/hdvg26/rlvol_8000M0042 - wrote new name
    Datafile /dev/hdvg27/rlvol_8000M0043 - wrote new name
    Datafile /dev/hdvg27/rlvol_8000M0044 - wrote new name
    Datafile /dev/hdvg27/rlvol_8000M0045 - wrote new name
    Datafile /dev/hdvg27/rlvol_8000M0047 - wrote new name
    Datafile /dev/hdvg27/rlvol_8000M0049 - wrote new name
    Datafile /dev/hdvg28/rlvol_8000M0050 - wrote new name
    Datafile /dev/hdvg28/rlvol_8000M0051 - wrote new name
    Datafile /dev/hdvg23/rlvol_4000M0017 - wrote new name
    Datafile /dev/hdvg21/rlvol_2000M0001 - wrote new name
    Datafile /dev/hdvg21/rlvol_2000M0003 - wrote new name
    Datafile /dev/hdvg22/rlvol_2000M0010 - wrote new name
    Datafile /dev/hdvg22/rlvol_2000M0012 - wrote new name
    Datafile /dev/hdvg22/rlvol_2000M0014 - wrote new name
    Datafile /dev/hdvg23/rlvol_2000M0016 - wrote new name
    Datafile /dev/hdvg23/rlvol_2000M0018 - wrote new name
    Datafile /dev/hdvg23/rlvol_2000M0019 - wrote new name
    Datafile /dev/hdvg23/rlvol_2000M0020 - wrote new name
    Datafile /dev/hdvg23/rlvol_2000M0021 - wrote new name
    Datafile /dev/hdvg23/rlvol_4000M0019 - wrote new name
    Datafile /dev/hdvg23/rlvol_4000M0020 - wrote new name
    Datafile /dev/hdvg24/rlvol_4000M0022 - wrote new name
    Datafile /dev/hdvg24/rlvol_2000M0022 - wrote new name
    Datafile /dev/hdvg24/rlvol_2000M0023 - wrote new name
    Datafile /dev/hdvg28/rlvol_8000M0054 - wrote new name
    Datafile /dev/hdvg28/rlvol_8000M0053 - wrote new name
    Datafile /dev/hdvg28/rlvol_8000M0055 - wrote new name
    Datafile /dev/hdvg28/rlvol_8000M0056 - wrote new name
    Datafile /dev/hdvg24/rlvol_4000M0023 - wrote new name
    Datafile /dev/hdvg24/rlvol_4000M0024 - wrote new name
    Datafile /dev/hdvg24/rlvol_4000M0026 - wrote new name
    Datafile /dev/hdvg24/rlvol_4000M0027 - wrote new name
    Datafile /dev/hdvg24/rlvol_4000M0028 - wrote new name
    Datafile /dev/hdvg25/rlvol_4000M0029 - wrote new name
    Datafile /dev/hdvg25/rlvol_4000M0030 - wrote new name
    Datafile /dev/hdvg25/rlvol_4000M0031 - wrote new name
    Datafile /dev/hdvg25/rlvol_4000M0033 - wrote new name
    Datafile /dev/hdvg25/rlvol_4000M0034 - wrote new name
    Datafile /dev/hdvg25/rlvol_4000M0035 - wrote new name
    Datafile /dev/hdvg26/rlvol_4000M0036 - wrote new name
    Datafile /dev/hdvg24/rlvol_2000M0024 - wrote new name
    Datafile /dev/hdvg24/rlvol_2000M0025 - wrote new name
    Datafile /dev/hdvg21/rlvol_2000M0005 - wrote new name
    Datafile /dev/hdvg29/rlvol_8000M0058 - wrote new name
    Datafile /dev/hdvg29/rlvol_8000M0059 - wrote new name
    Datafile /dev/hdvg29/rlvol_8000M0060 - wrote new name
    Datafile /dev/hdvg29/rlvol_8000M0061 - wrote new name
    Datafile /dev/hdvg29/rlvol_8000M0062 - wrote new name
    Datafile /dev/hdvg29/rlvol_8000M0063 - wrote new name
    Datafile /dev/hdvg30/rlvol_8000M0064 - wrote new name
    Datafile /dev/hdvg26/rlvol_4000M0038 - wrote new name
    Datafile /dev/hdvg26/rlvol_4000M0039 - wrote new name
    Datafile /dev/hdvg26/rlvol_4000M0040 - wrote new name
    Datafile /dev/hdvg26/rlvol_4000M0041 - wrote new name
    Datafile /dev/hdvg27/rlvol_4000M0047 - wrote new name
    Datafile /dev/hdvg27/rlvol_4000M0048 - wrote new name
    Datafile /dev/hdvg27/rlvol_4000M0049 - wrote new name
    Datafile /dev/hdvg28/rlvol_4000M0050 - wrote new name
    Datafile /dev/hdvg21/rlvol_1000M0001 - wrote new name
    Datafile /dev/hdvg21/rlvol_1000M0002 - wrote new name
    Datafile /dev/hdvg24/rlvol_2000M0026 - wrote new name
    Datafile /dev/hdvg24/rlvol_2000M0027 - wrote new name
    Datafile /dev/hdvg21/rlvol_1000M0003 - wrote new name
    Datafile /dev/hdvg21/rlvol_1000M0004 - wrote new name
    Datafile /dev/hdvg21/rlvol_2000M0006 - wrote new name
    Datafile /dev/hdvg24/rlvol_2000M0028 - wrote new name
    Datafile /dev/hdvg25/rlvol_2000M0029 - wrote new name
    Datafile /dev/hdvg30/rlvol_8000M0066 - wrote new name
    Datafile /dev/hdvg30/rlvol_8000M0067 - wrote new name
    Datafile /dev/hdvg21/rlvol_1000M0005 - wrote new name
    Datafile /dev/hdvg21/rlvol_1000M0006 - wrote new name
    Datafile /dev/hdvg21/rlvol_4000M0006 - wrote new name
    Datafile /dev/hdvg27/rlvol_4000M0044 - wrote new name
    Datafile /dev/hdvg28/rlvol_4000M0052 - wrote new name
    Datafile /dev/hdvg30/rlvol_8000M0069 - wrote new name
    Datafile /dev/hdvg30/rlvol_8000M0070 - wrote new name
    Datafile /dev/hdvg27/rlvol_8000M0048 - wrote new name
    Datafile /dev/hdvg28/rlvol_4000M0054 - wrote new name
    Datafile /dev/hdvg28/rlvol_4000M0055 - wrote new name
    Datafile /dev/hdvg27/rlvol_4000M0043 - wrote new name
    Datafile /dev/hdvg30/rlvol_4000M0070 - wrote new name
    Datafile /dev/hdvg25/rlvol_8000M0033 - wrote new name
    Datafile /dev/hdvg25/rlvol_8000M0029 - wrote new name
    Datafile /dev/hdvg26/rlvol_8000M0039 - wrote new name
    Datafile /dev/hdvg23/rlvol_4000M0018 - wrote new name
    Datafile /dev/hdvg23/rlvol_4000M0021 - wrote new name
    Datafile /dev/hdvg24/rlvol_4000M0025 - wrote new name
    Datafile /dev/hdvg26/rlvol_4000M0037 - wrote new name
    Datafile /dev/hdvg27/rlvol_4000M0045 - wrote new name
    Datafile /dev/hdvg27/rlvol_4000M0046 - wrote new name
    Datafile /dev/hdvg25/rlvol_2000M0030 - wrote new name
    Datafile /dev/hdvg25/rlvol_2000M0032 - wrote new name
    Datafile /dev/hdvg25/rlvol_2000M0033 - wrote new name
    Datafile /dev/hdvg28/rlvol_4000M0056 - wrote new name
    Datafile /dev/hdvg29/rlvol_4000M0057 - wrote new name
    Datafile /dev/hdvg29/rlvol_4000M0058 - wrote new name
    Datafile /dev/hdvg29/rlvol_4000M0059 - wrote new name
    Datafile /dev/hdvg29/rlvol_4000M0060 - wrote new name
    Datafile /dev/hdvg29/rlvol_4000M0061 - wrote new name
    Datafile /dev/hdvg29/rlvol_4000M0062 - wrote new name
    Datafile /dev/hdvg22/rlvol_4000M0012 - wrote new name
    Datafile /dev/hdvg22/rlvol_4000M0014 - wrote new name
    Datafile /dev/hdvg21/rlvol_2000M0002 - wrote new name
    Datafile /dev/hdvg28/rlvol_4000M0051 - wrote new name
    Datafile /dev/hdvg29/rlvol_4000M0063 - wrote new name
    Datafile /dev/hdvg21/rlvol_2000M0004 - wrote new name
    Datafile /dev/hdvg25/rlvol_2000M0034 - wrote new name
    Datafile /dev/hdvg25/rlvol_2000M0035 - wrote new name
    Datafile /dev/hdvg26/rlvol_2000M0036 - wrote new name
    Datafile /dev/hdvg23/rlvol_4000M0016 - wrote new name
    Datafile /dev/hdvg22/rlvol_1000M0007 - wrote new name
    Datafile /dev/hdvg26/rlvol_2000M0037 - wrote new name
    Datafile /dev/hdvg22/rlvol_8000M0010 - wrote new name
    Datafile /dev/hdvg21/rlvol_2000M0007 - wrote new name
    Datafile /dev/hdvg25/rlvol_4000M0032 - wrote new name
    Datafile /dev/hdvg28/rlvol_4000M0053 - wrote new name
    Datafile /dev/hdvg22/rlvol_2000M0011 - wrote new name
    Datafile /dev/hdvg22/rlvol_2000M0013 - wrote new name
    Datafile /dev/hdvg23/rlvol_2000M0015 - wrote new name
    Datafile /dev/hdvg30/rlvol_4000M0064 - wrote new name
    Datafile /dev/hdvg30/rlvol_4000M0065 - wrote new name
    Datafile /dev/hdvg30/rlvol_4000M0066 - wrote new name
    Datafile /dev/hdvg30/rlvol_4000M0067 - wrote new name
    Datafile /dev/hdvg30/rlvol_4000M0069 - wrote new name
    Datafile /dev/hdvg30/rlvol_4000M0068 - wrote new name
    Datafile /dev/hdvg26/rlvol_2000M0038 - wrote new name
    Datafile /dev/hdvg26/rlvol_2000M0039 - wrote new name
    Datafile /dev/hdvg23/rlvol_2000M0017 - wrote new name
    Datafile /dev/hdvg25/rlvol_2000M0031 - wrote new name
    Datafile /dev/hdvg26/rlvol_2000M0040 - wrote new name
    Datafile /dev/hdvg26/rlvol_2000M0041 - wrote new name
    Datafile /dev/hdvg26/rlvol_2000M0042 - wrote new name
    Datafile /dev/hdvg27/rlvol_2000M0043 - wrote new name
    Datafile /dev/hdvg28/rlvol_1000M0044 - wrote new name
    Datafile /dev/hdvg28/rlvol_1000M0045 - wrote new name
    Datafile /dev/hdvg27/rlvol_2000M0044 - wrote new name
    Datafile /dev/hdvg27/rlvol_2000M0045 - wrote new name
    Datafile /dev/hdvg27/rlvol_2000M0046 - wrote new name
    Datafile /dev/hdvg27/rlvol_2000M0047 - wrote new name
    Datafile /dev/hdvg27/rlvol_2000M0048 - wrote new name
    Datafile /dev/hdvg27/rlvol_2000M0049 - wrote new name
    Datafile /dev/hdvg28/rlvol_2000M0050 - wrote new name
    Datafile /dev/hdvg28/rlvol_2000M0051 - wrote new name
    Datafile /dev/hdvg28/rlvol_2000M0052 - wrote new name
    Datafile /dev/hdvg28/rlvol_2000M0053 - wrote new name
    Datafile /dev/hdvg28/rlvol_2000M0054 - wrote new name
    Datafile /dev/hdvg28/rlvol_2000M0055 - wrote new name
    Datafile /dev/hdvg21/rlvol_8000M0001 - wrote new name
    Datafile /dev/hdvg27/rlvol_8000M0046 - wrote new name
    Datafile /dev/hdvg28/rlvol_8000M0052 - wrote new name
    Datafile /dev/hdvg29/rlvol_8000M0057 - wrote new name
    Datafile /dev/hdvg30/rlvol_8000M0065 - wrote new name
    Datafile /dev/hdvg30/rlvol_8000M0068 - wrote new name
    Control File /dev/hdvg21/rlvol_0500M0001 - wrote new name
    Control File /dev/hdvg22/rlvol_0500M0006 - wrote new name
    Control File /dev/hdvg23/rlvol_0500M0011 - wrote new name
    Instance shut down

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

 

11) .profile에 설정되어 있는 SID를 TEST로 변경합니다.   

 

12) sqlplus 에 로그인하고, startup 명령으로 DB를 살려서 정상적으로 올라오는지 확인합니다.

    (pfile을 가지고 spfile을 생성한 후 다시 startup하여 spfile로 DB가 운영되도록 수정합니다.)

   SQL>startup pfile=inittbizdb.ora
   SQL>create spfile from pfile;
   SQL>shutdown immediate
   SQL>startup

 

13) 아무런 문제가 없다면 작업 종료...

 

일반적으로 SID만 변경하시는 분들은 8)부터만 수행하시면 됩니다. 1)~7)까지는 이미지카피 후 정합성 확인하는 부분입니다.

 

좀 더 자세한 내용은 nid로 오라클 사이트에서 검색하시면 찾으실 수 있을 겁니다....

 

업무에 도움이 되셨길 빕니다....^^

[출처] 간단한 방법으로 SID 변경하기 (nid 사용) (ProDBA) |작성자 바람숨결