'DB'에 해당되는 글 44건

UPDATE user set password=password("test2022ccw") where user = 'root';
commit

flush privileges;

DB 백업

mysqldump (--default-character-set=현재CharSet) -u UserId -p DB명 > 백업할파일명.sql

 

DB 복원

mysql (--default-character-set=DB의CharSet) -u UserId -p DB명 < 백업한파일명.sql

 

 ※ 백업이나 복원할 DB 또는 TABLE의 CharSet이 같다면 위의 ()를 생략해도 되며 한글이 깨질 경우 ()를 입력해야 함



ex) mysql -u ccw -p --default-character-set=utf8 ccwtest_db < ccw2022.sql

mysqldump -u [user_name] -p [백업할 db_name] > [저장될 파일명]

> mysqldump -u test_user -p ccwtestdb > ccwtestdb.sql

password : 암호를 넣는다

 

* 복구

mysql -u [user_name] -p [백업할 db_name] < ccwtestdb.sql

> mysql -u test_user -p ccwtestdb < ccwtestdb.sql

 

이렇게 복구를 했는데도 한글이 깨지는 경우

* EUCKR 인 경우

mysql -u test_user -p --default-character-set=euckr ccwtestdb < ccwtestdb.sql

* UTF-8인 경우

mysql -u test_user -p --default-character-set=utf8 ccwtestdb < ccwtestdb.sql

mysqldump -u root -p1111 club > "C:\Program Files\MySQL\MySQL Server 5.0\backup\%DATE%_club_backup.sql"

bat 파일로 저장 후 

예약 작업에 스케줄러 작업

권한 확인
SELECT *
  FROM USER_TAB_PRIVS;
  
 비밀번호 강제 변경 
 sqlplus "/as sysdba"

ALTER USER 아이디 IDENTIFIED BY 암호;

SELECT A.SESSION_ID SID,
B.SERIAL# SERIAL_NO,
A.OS_USER_NAME OS_USER_NAME,
A.ORACLE_USERNAME ORACLE_USERNAME,
B.STATUS STATUS
FROM V$LOCKED_OBJECT A, V$SESSION B
WHERE A.SESSION_ID = B.SID;

SELECT *
  FROM V$LOCKED_OBJECT;

ALTER SYSTEM KILL SESSION 'SID, SERIAL_ID';

※DBA_FREE_SPACE, DBA_DATA_FILES 데이터 사전 

SELECT b.file_name "FILE_NAME", -- DataFile Name 
       b.tablespace_name "TABLESPACE_NAME", -- TableSpace Name 
       b.bytes / 1024 "TOTAL SIZE(KB)", -- 총 Bytes 
       ((b.bytes - sum(nvl(a.bytes,0)))) / 1024 "USED(KB)", -- 사용한 용량 
       (sum(nvl(a.bytes,0))) / 1024 "FREE SIZE(KB)", -- 남은 용량 
       ROUND((sum(nvl(a.bytes,0)) / (b.bytes)) * 100, 2) "FREE %" -- 남은 % 
  FROM DBA_FREE_SPACE a, DBA_DATA_FILES b 
WHERE a.file_id(+) = b.file_id 
GROUP BY b.tablespace_name, b.file_name, b.bytes ORDER BY b.tablespace_name

오라클 impdp, expdp 사용법

* 초기작업 *
- sys계정접속
create directory ccw_dir as 'D:\ccw_dbdump';  (디렉토리 생성)
grant read,write on directory to ccw_test;    (해당 user 디렉토리접근 권한부여)

1. expdp 

expdp 유저아이디/유저패스워드@SID dumpfile=덤프명.dmp logfile=로그명.log directory=디렉토리이름(data_pump_dir) version=10.2.0.1.0

2.  impdp

impdp 유저아이디/유저패스워드 dumpfile=덤프명.dmp REMAP_SCHEMA=exp유저:imp유저 REMAP_TABLESPACE=exp테이블스페이스명:imp테이블스페이스명

* 명령어 *
expdp CCW_TEST/CCW_TEST@ORCL dumpfile=ccw_20150910.dmp log_file=ccw_20150910.log directory=ccw_dir

impdp CCW_TEST/CCW_TEST dumpfile=ccw_20150910.dmp REMAP_SCHEMA=CCW_TEST:CCW_TEST REMAP_TABLESPACE=CCW_TAB_TBS:TSDT_CCW


참조url :http://koeiking11.tistory.com/189

Oracle DataBase 문자셋과 언어셋 확인 및 변경하기

Oracle DataBase 문자셋과 언어셋 확인하기
[ 문자셋 확인 ]
   SELECT name, value$
   FROM sys.props$ 
   WHERE name = 'NLS_CHARACTERSET'; 

   SELECT name, value$
   FROM sys.props$ 
   WHERE name = 'NLS_NCHAR_CHARACTERSET'; 

[ 언어셋 확인 ]
   SELECT name, value$
   FROM sys.props$ 
   WHERE name = 'NLS_LANGUAGE'; 

Oracle DataBase 문자셋과 언어셋 변경하기
- 문자셋 변경 : Oracle DataBase 관리자로 접속하여 NLS_CHARACTERSET, NCHAR의 CHARACTERSET에 원하는 문자셋을 변경할 수 있다.
- 언어셋 변경 : 문자셋과 마찬가지로 오라클 데이터베이스 관리자로 접속하여 NLS_LANGUAGE에 원하는 언어셋을 변경할 수 있다.

[ 문자셋 변경 ]
   UPDATE sys.props$
   SET value$ = '[ 변경을 원하는 문자셋 (KO16KSC5601, AL32UTF8 등)]'
   WHERE name = 'NLS_CHARACTERSET';

   UPDATE sys.props$
   SET value$ = '[ 변경을 원하는 문자셋 (KO16KSC5601, AL32UTF8 등)]'
   WHERE name = 'NLS_NCHAR_CHARACTERSET';

[ 언어셋 변경 ]
   UPDATE sys.props$
   SET value$ = '[ 변경을 원하는 문자셋 (AMERICAN_AMERICA.KO16KSC5601, AMERICAN_AMERICA.AL32UTF8등)]'
   WHERE name = 'NLS_LANGUAGE'';

출처 : Tong - 시우세상님의 Oracle통

 

Oracle Character set 변경 자세히

####################################################
#### .bash_profile 환경변수
    export ORACLE_BASE=/home/oracle
    export ORACLE_HOME=$ORACLE_BASE/oracle/product/10.2.0/db_1
    export ORACLE_SID=ora10
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib
    #export LD_ASSUME_KERNEL=2.4.19
    export PATH=$PATH:$ORACLE_HOME/bin
    
    export NLS_LANG=KOREAN_KOREA.KO16KSC5601       #한글
    export NLS_LANG=KOREAN_KOREA.KO16MSWIN949    #한글(추천:지원캐릭터가 더 많음 -뷃,숖..)
    export NLS_LANG=AMERICAN_AMERICA.UTF8            #유니코드


    
####################################################
#### 캐릭터 셋 설정 확인

    SELECT NAME,VALUE$ FROM PROPS$ WHERE NAME ='NLS_LANGUAGE' OR NAME ='NLS_TERRITORY' OR NAME ='NLS_CHARACTERSET';


####################################################
#### 오라클 캐릭터 셋 변경

    update props$ set VALUE$='UTF-8' where name='NLS_CHARACTERSET';
    update props$ set VALUE$='KO16MSWIN949' where name='NLS_CHARACTERSET';
    update props$ set VALUE$='KO16KSC5601' where name='NLS_CHARACTERSET';
    
    update props$ set VALUE$='KOREAN' where name='NLS_LANGUAGE';
    update props$ set VALUE$='KOREA' where name='NLS_TERRITORY';
    


####################################################
#### 캐릭터 셋 변경후에 확인 사항(필수)

    에러 유형 : 
        EXP-00008: ORACLE 오류 6552가 발생했습니다
        ORA-06552: PL/SQL: Compilation unit analysis terminated
        ORA-06553: PLS-553: 알 수 없는 문자 집합 이름입니다


    Problem description
    ===================
    You receive the following error when (re)compiling or calling a piece of pl/sql:
    ORA-06550: line <num>, column <num>: ....
    or
    ORA-06552: PL/SQL: Compilation unit analysis terminated
    followed by
    ORA-06553: PLS-553: character set name is not recognized
  에러 원인 :  character set 이 섞여있음.

 

    캐릭터셋 확인 쿼리 : 
    select distinct(nls_charset_name(charsetid)) CHARACTERSET,
           decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
                         9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
                        96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
                       112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
       from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112);

    캐릭터셋 확인 쿼리 결과(잘못된 경우): varchar2가 2개 의 캐릭터셋이 설정되어있음.
        CHARACTERSET                            TYPES_USED_IN
        -----------------------------------------------------
        AL16UTF16                               NCHAR
        AL16UTF16                               NVARCHAR2
        AL16UTF16                               NCLOB
        US7ASCII                                CHAR
        US7ASCII                                VARCHAR2
        WE8DEC                                 VARCHAR2
        US7ASCII                                CLOB
        
        
    캐릭터셋 확인 쿼리 결과(정상인 경우): TYPES_USERD_IN 하나당 하나의 캐릭터셋
        CHARACTERSET                            TYPES_USED_IN
        -----------------------------------------------------
        AL16UTF16                               NCHAR
        AL16UTF16                               NVARCHAR2
        AL16UTF16                               NCLOB
        AL32UTF8                                CHAR
        AL32UTF8                                VARCHAR2
        AL32UTF8                                CLOB


    해결 방법 : 

        1. INIT.ORA 안에 있는 parallel_server parameter 가 false 거나 아예 세팅되어있지 않은지 확인.
           SQL>show parameter parallel_server

        2. sqlplus "/as sysdba"로 다음 쿼리 실행(기존 데이터 백업 필수)
           SHUTDOWN IMMEDIATE;
           STARTUP MOUNT;
           ALTER SYSTEM ENABLE RESTRICTED SESSION;
           ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
           ALTER SYSTEM SET AQ_TM_PROCESSES=0;
           ALTER DATABASE OPEN;
           COL VALUE NEW_VALUE CHARSET
           SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
           COL VALUE NEW_VALUE NCHARSET
           SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
   
           -- UTF8로 바꿀 경우(선택)
           ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
           ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
   
             -- 한글로 바꿀 경우(선택)
           ALTER DATABASE CHARACTER SET INTERNAL_USE KO16MSWIN949
           ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
           
           -- oracle reboot 2번.
           SHUTDOWN IMMEDIATE;
           STARTUP;
           SHUTDOWN IMMEDIATE;
           STARTUP;

        3. parallel_server parameter 수정한 경우 원복.

regexp_replace(a1.SCNCENM,'[가-힛]','')