pat98's always & forever

RAC DB의 INSTSNCE_NAME 및 DB_NAME 을 변경해 보자.

Single 과 크게 다를것은 없다. nid 를 이용하여 변경하면 되고 cluter_database=false 후 parameter 재설정 작업만 해주면 된다. 그외 리소스 삭제후 추가작업

-- 먼저 INSTANCE 이름을 바꾸어 본다.

현재 RAC1,RAC2 를 TEST1,TEST2 로 바꾸자

1. 현재 parameter file 확인

------------------------------------ ---------------------- ------------------------------
spfile                               string                 +DATA/RAC/PARAMETERFILE/spfile
                                                            .270.910889297
2. 현재 Instance 이름 확인

sys@RAC1> show parameter instance_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
instance_name                        string                 RAC1

sys@RAC1> alter system set instance_name=TEST1 scope=spfile sid='RAC1';

System altered.

Elapsed: 00:00:00.16

3. 재기동 한후 확인, 별거없다. 바뀌었다.

sys@RAC1> show parameter instance_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
instance_name                        string                 TEST1

sys@RAC1> show parameter db_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_name                              string                 RAC
sys@RAC1> show parameter db_unique

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name                       string                 RAC

sys@RAC1> SELECT instance FROM v$thread;

INSTANCE
-------------------------------------------------------------------------------------------
RAC1
RAC2

2번 노드도 동일하게 작업한다.

sys@RAC2> alter system set instance_name=TEST2 scope=spfile sid='RAC2';

.....

작업후 변경확인..잘된다.

-- 이제 DB_NAME 도 바꾸어 보겠다.

1. pfile 생성 후 내용수정

SQL> create pfile from spfile ;

생성된 pfile 을 수정해서 RAC1,RAC2 로 된 부분을 모두 TEST1,2 로 변경한다.

RAC1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# vi initTEST1.ora
TEST1.__data_transfer_cache_size=0
TEST2.__data_transfer_cache_size=0
TEST1.__db_cache_size=83886080
TEST2.__db_cache_size=130023424
TEST1.__java_pool_size=4194304
TEST2.__java_pool_size=4194304
TEST1.__large_pool_size=4194304
TEST2.__large_pool_size=4194304
TEST1.__oracle_base='/u01/app/oracle'#OTESTLE_BASE set from environment
TEST2.__oracle_base='/u01/app/oracle'#OTESTLE_BASE set from environment
TEST1.__pga_aggregate_target=104857600
TEST2.__pga_aggregate_target=104857600
TEST1.__sga_target=419430400
TEST2.__sga_target=419430400
TEST1.__shared_io_pool_size=8388608
TEST2.__shared_io_pool_size=12582912
TEST1.__shared_pool_size=310378496
TEST2.__shared_pool_size=260046848
TEST1.__streams_pool_size=0
TEST2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/TEST/adump'
*.audit_sys_operations=FALSE
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='12.1.0'
*.control_files='+DATA/rac/controlfile/current.256.907339295','+RECO/rac/controlfile/current.256.907339297'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='TEST'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/u01/app/oracle'
TEST2.instance_number=2
TEST1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.sga_target=419430400
TEST2.thread=2
TEST1.thread=1
TEST2.undo_tablespace='UNDOTBS2'
TEST1.undo_tablespace='UNDOTBS1'

2. password 화일 복사, adump directory 생성, 기존 initRAC1 화일 백업

[RAC1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# mkdir -p /u01/app/oracle/admin/TEST/adump
[RAC1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# cp orapwRAC1 orapwTEST1
[RAC1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# cp initRAC1.ora  initRAC1.ora.old

3. 양쪽 모두 내린다.

srvctl stop database -d RAC

4. exclusive 로 1번 startup

idle> startup mount exclusive pfile='/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/initRAC1.ora

ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size                  2925120 bytes
Variable Size             318770624 bytes
Database Buffers           92274688 bytes
Redo Buffers                5459968 bytes
Database mounted.

5. nid 로 변경

[RAC1]oracle@rac1:/home/oracle# nid target=sys/oracle dbname=TEST setname=yes

DBNEWID: Release 12.1.0.2.0 - Production on Tue May 17 10:56:34 2016

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

Connected to database RAC (DBID=2495321304)

Connected to server version 12.1.0

Control Files in database:
    +DATA/rac/controlfile/current.256.907339295
    +RECO/rac/controlfile/current.256.907339297

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

Proceeding with operation
Changing database name from RAC to TEST
    Control File +DATA/rac/controlfile/current.256.907339295 - modified
    Control File +RECO/rac/controlfile/current.256.907339297 - modified
    Datafile +DATA/rac/datafile/system.259.90733933 - wrote new name
    Datafile +DATA/rac/datafile/sysaux.260.90733937 - wrote new name
    Datafile +DATA/rac/datafile/undotbs1.261.90733940 - wrote new name
    Datafile +DATA/rac/datafile/undotbs2.263.90733943 - wrote new name
    Datafile +DATA/rac/datafile/users.264.90733943 - wrote new name
    Datafile +DATA/rac/tempfile/temp.262.90733941 - wrote new name
    Control File +DATA/rac/controlfile/current.256.907339295 - wrote new name
    Control File +RECO/rac/controlfile/current.256.907339297 - 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.

6. profile의 환경변수 TEST로 변경, cluster_database=false 로 하고 pfile로 기동,

*.cluster_database=false
 
idle> startup pfile=/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/initTEST1.ora;
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size                  2925120 bytes
Variable Size             318770624 bytes
Database Buffers           92274688 bytes
Redo Buffers                5459968 bytes
Database mounted.
Database opened.

7. spfile 을 맹글어 준다.

idle> create spfile='+DATA' from pfile;

File created.

asmcmd -p 로 +ASM1 접속, 화일 확인

Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   MAY 17 11:00:00  Y    none => spfile.271.912078483
PARAMETERFILE  UNPROT  COARSE   MAY 17 11:00:00  Y    none => spfile.272.912079201
PARAMETERFILE  UNPROT  COARSE   MAY 17 11:00:00  Y    none => spfile.273.912080113

8. pfile에 spfile 경로를 적어준다.

[TEST1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# vi initTEST1.ora
SPFILE='+DATA/TEST/PARAMETERFILE/spfile.273.912080113'

7. startup 한후에 다시 내린다.

idle> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size                  2925120 bytes
Variable Size             318770624 bytes
Database Buffers           92274688 bytes
Redo Buffers                5459968 bytes
Database mounted.
Database opened.

9. 2번 노드도 nid 실행하는거 빼고 나머지 절차를 동일하게 진행한다. 잘된다.

10. cluster_database=true 로 변경후 재기동

SQL> alter system set cluster_database=true scope=spfile sid='*';

11. CRS RESOURCE에서 바뀐 INSTANCE 를 인식못하니 기존것 삭제후 새로 등록한다.

ora.rac.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
12. 기존꺼 삭제 (쫄지 말자, 이건 DB에 아무런 영향을 주지 않는다)

[TEST1]oracle@rac1:/home/oracle# srvctl remove instance -d RAC -i RAC1
Remove instance from the database RAC? (y/[n]) y
[TEST1]oracle@rac1:/home/oracle# srvctl remove instance -d RAC -i RAC2
Remove instance from the database RAC? (y/[n]) y
[TEST1]oracle@rac1:/home/oracle# srvctl remove database -d RAC
Remove the database RAC? (y/[n]) y

13. 새로운거 추가

[TEST1]oracle@rac1:/home/oracle# srvctl add database -d TEST -o /u01/app/oracle/product/12.1.0.2/dbhome_1

[TEST1]oracle@rac1:/home/oracle# srvctl add instance -d TEST -i TEST1 -n rac1
[TEST1]oracle@rac1:/home/oracle# srvctl add instance -d TEST -i TEST2 -n rac2

14. RESOURCE에서 바로 인식 못할수 있으니 깔끔하게 crsctl stop crs 해서 다시 start 해준다.

15. 정상 등록 확인

ora.test.db
      1        ONLINE  ONLINE       rac1                     Open,STABLE
      2        ONLINE  ONLINE       rac2                     Open,STABLE

16. 적용된 값들 확인

sys@TEST1> show parameter instance_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
instance_name                        string                 TEST1
sys@TEST1> show parameter db_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_name                              string                 TEST
sys@TEST1> show parameter db_unique

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name                       string                 TEST

17. 작업후기

RAC 도 별거 없이 잘 바뀌며, 작업시 parameter 백업, cluster_database=true,false 만 manage 잘하면 하면 OK ~~

SEVICE_NAME 등에 변경내역 반영, 접속 확인할 것.

1. TNSNAMES.ORA 반영

EXA =

   (DESCRIPTION =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.20.3)(PORT = 1521))

     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.20.4)(PORT = 1521))

     (LOAD_BALANCE = yes)

     (CONNECT_DATA =

       (SERVER = DEDICATED)

       (SERVICE_NAME = EXA)

     )

   )

2. test.sh 작성

vi test.sh

#!/bin/sh

count=0

while [ $count -lt 5 ]                                           # 5번 수행하겠다.

do                                                               # loop 시작

    count=`expr $count + 1`                                   # 카운터 증가 

    sqlplus -S system/welcome1@EXA @test.sql 

done

3. test.sql 작성

col "Instance" format a20

col "Host" format a20

col "Service Name" format a20

select sys_context('userenv', 'instance_name') "Instance",

       sys_context('userenv', 'server_host') "Host",

       sys_context('userenv', 'service_name') "Service Name"

from dual

/

exit;

4. sh test.sh 실행

[EXA1]oracle@exa1:/home/oracle# sh loop.sh

Instance             Host                 Service Name

-------------------- -------------------- --------------------

EXA2                 exa2                 EXA

Instance             Host                 Service Name

-------------------- -------------------- --------------------

EXA1                 exa1                 EXA

Instance             Host                 Service Name

-------------------- -------------------- --------------------

EXA1                 exa1                 EXA

Instance             Host                 Service Name

-------------------- -------------------- --------------------

EXA2                 exa2                 EXA

Instance             Host                 Service Name

-------------------- -------------------- --------------------

EXA1                 exa1                 EXA

RAC cluster 에서 로드밸런스가 잘 되고 있음을 확인할수 있다.