Oracle service name 변경 - Oracle service name byeongyeong

service name 변경 및 추가

서비스 네임 추가

SQL> ALTER system set service_names='orcl.oraedu.com,kbs.oraedu.com';    -> 서비스 네임 추가

System altered.

SQL> show parameter service_names

NAME                                 TYPE        VALUE

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

service_names                        string      orcl.oraedu.com,kbs.oraedu.com

서비스 네임 변경

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      prod.oraedu.com

SQL> ALTER system set service_names='prod';

System altered.

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      prod

SQL>

oracle db_name 변경하기(ORACLE_SID변경)

기존 db_name : chora10

변경할 db_name : ora10ex

1. instance name, database name확인

SQL> select instance from v$thread;

INSTANCE

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

chora10

SQL> select name from v$database;

NAME

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

CHORA10

2. pfile 생성 및 수정

SQL> create pfile from spfile;

File created.

SQL> quit

[test.oracle.com:/oracle] cd $ORACLE_HOME/dbs

[test.oracle.com:/oracle/product/102/dbs] mv initchora10.ora initora10ex.ora

[test.oracle.com:/oracle/product/102/dbs] vi initora10ex.ora

:%s/chora10/ora10ex/g

....

....

....

*.audit_file_dest='/oracle/admin/ora10ex/adump'

*.background_dump_dest='/oracle/admin/ora10ex/bdump'

*.compatible='10.2.0.3.0'

*.control_files='/oradata/ora10ex/control01.ctl','/oradata/ora10ex/control02.ctl','/oradata/ora10ex/control03.ctl'

*.core_dump_dest='/oracle/admin/ora10ex/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='ora10ex'

....

....

....

3. 새로운 database name에 대한 환경 설정

[test.oracle.com:/oracle] cd $ORACLE_BASE/admin

[test.oracle.com:/oracle/admin] mkdir ora10ex

[test.oracle.com:/oracle/admin] cd ora10ex   

[test.oracle.com:/oracle/admin/ora10ex] mkdir -p adump bdump cdump udump   <-- dumpfile공간생성

[test.oracle.com:/oracle/admin/ora10ex] cd /oradata                 

[test.oracle.com:/oradata] mkdir ora10ex   <-- datafile, redo, control file 저장공간

[test.oracle.com:/oradata] cd ora10ex

[test.oracle.com:/oradata/ora10ex] mkdir arch   <-- archive file 저장공간 

4. database file이동

[test.oracle.com:/oradata] cd chora10   <-- database file이 있는 곳으로 이동

[test.oracle.com:/oradata/chora10] cp *.* ../ora10ex

[test.oracle.com:/oradata/chora10] cd ../ora10ex

[test.oracle.com:/oradata/ora10ex] rm *.ctl   <-- control file 삭제

5. controlfile 생성 및 수정

[test.oracle.com:/oradata/ora10ex] sqlplus "/as sysdba"

SQL> alter system switch logfile;

System altered.

SQL> alter database backup controlfile to trace resetlogs;

SQL > shudown immediate

SQL> quit

[test.oracle.com:/oradata/ora10ex] cd $ORACLE_BASE/admin/ora10ex/udump

[test.oracle.com:/oracle/admin/ora10ex/udump] ls -altr

가장 최근에 생성된 trace file의 이름을 변경한다.

[test.oracle.com:/oracle/admin/ora10ex/udump] mv ora10ex_ora_7381.trc ccf.sql

ccf.sql file을 다음과 같이 수정한다.

FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS ...

TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ...   <--reuse를 set으로 변경

FROM:

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

TO:

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.

# RECOVER DATABASE USING BACKUP CONTROLFILE   <--주석처리

CREATE CONTROLFILE set DATABASE..........

...ALTER DATABASE OPEN RESETLOGS; 외의 내용은 다 삭제한다.

:%s/chora10/ora10ex/g

6. ORACLE_SID변경 및 database open

[test.oracle.com:/oracle] export ORACLE_SID=ora10ex

[test.oracle.com:/oracle] sqlplus "/as sysdba"

SQL> @ccf.sql

Control file created.

Database altered.

SQL> select instance from v$thread;

INSTANCE

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

ora10ex

SQL> select name from v$database;

NAME

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

ORA10EX

끝.

출처: https://homeboy.tistory.com/entry/oracle-dbname-변경하기ORACLESID변경 [Hand me the Key!!]