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!!] |