{"id":5818,"date":"2023-04-06T23:39:40","date_gmt":"2023-04-06T14:39:40","guid":{"rendered":"\/blog\/?p=5818"},"modified":"2023-11-29T14:11:41","modified_gmt":"2023-11-29T05:11:41","slug":"oracle-db-%eb%b0%b1%ec%97%85-%eb%b3%b5%ea%b5%ac","status":"publish","type":"post","link":"https:\/\/hasu0707.duckdns.org\/blog\/?p=5818","title":{"rendered":"SSC Database \ubc31\uc5c5\/\ubcf5\uad6c"},"content":{"rendered":"\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">\u203b \"ssc_2222\"\ub97c \ud574\ub2f9 \ud0a4\uc6cc\ub4dc\ub85c \ubcc0\uacbd \ud6c4 \uc0ac\uc6a9\n\u203b \"&lt;PASSWORD>\"\ub97c \ube44\ubc00\ubc88\ud638\ub85c \ubcc0\uacbd \ud6c4 \uc0ac\uc6a9\n\n#################################################\n#\n# Oracle DB \ubc31\uc5c5\n#\n#################################################\nsqlplus \"system\/manager as sysdba\"\n  SELECT * FROM DBA_DIRECTORIES;\n  CREATE DIRECTORY DIR_SSC_BACKUP AS 'C:\\database\\backup\\oracle';\n  GRANT READ,WRITE ON DIRECTORY DIR_SSC_BACKUP TO u_ssc_2222;\n\nMKDIR C:\\database\\backup\\oracle\nexpdp \"u_ssc_2222\/&lt;PASSWORD>@ORCL\" SCHEMAS=u_ssc_2222 DIRECTORY=DIR_SSC_BACKUP DUMPFILE=db_ssc_2222 LOGFILE=db_ssc_2222\n\n#################################################\n#\n# Oracle DB \ubcf5\uc6d0\n#\n#################################################\nsqlplus \"system\/manager as sysdba\"\n  -- \uc2a4\ud0a4\ub9c8 \ubc0f \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4 \uc0e5\uc81c\n  -- Oracle 12c\uc758 \uc774\uc804\ubc84\uc804 \ud638\ud658\uc744 \uc704\ud55c \uc124\uc815\n  ALTER SESSION SET \"_ORACLE_SCRIPT\"=TRUE;\n  DROP USER u_ssc_2222 CASCADE;\n  DROP TABLESPACE ts_ssc_2222 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;\n\n  -- \uc2a4\ud0a4\ub9c8 \ubc0f \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4 \ub2e4\uc2dc \uc0dd\uc131\n  ALTER SESSION SET \"_ORACLE_SCRIPT\"=TRUE;\n  CREATE TABLESPACE ts_ssc_2222 DATAFILE 'C:\\database\\oradata\\orcl\\ts_ssc_2222.dbf' SIZE 2M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;\n  CREATE USER u_ssc_2222 IDENTIFIED BY \"&lt;PASSWORD>\";\n  ALTER USER u_ssc_2222 DEFAULT TABLESPACE ts_ssc_2222;\n  ALTER USER u_ssc_2222 TEMPORARY TABLESPACE TEMP;\n  GRANT CONNECT, DBA, RESOURCE TO u_ssc_2222;\n\n# \ubcf5\uc6d0\nimpdp \"u_ssc_2222\/&lt;PASSWORD>@ORCL\" SCHEMAS=u_ssc_2222 DIRECTORY=DIR_SSC_BACKUP DUMPFILE=db_ssc_2222.dmp LOGFILE=db_ssc_2222.log\n\nsqlplus \"system\/manager as sysdba\"\n  -- \ud544\uc694\ud55c \uacbd\uc6b0 \ub514\ub809\ud1a0\ub9ac \uc0ad\uc81c\n  DROP DIRECTORY DIR_SSC_BACKUP;\n\nREM ---------------------------------------------\n\n#################################################\n#\n# Microsoft SQL DB \ubc31\uc5c5\n#\n#################################################\nsqlcmd -S tcp:10.10.10.105 -U SA -P&lt;PASSWORD> -Q \"BACKUP DATABASE [db_ssc_2222] TO DISK = N'C:\\database\\backup\\mssql\\db_ssc_2222.bak' WITH NOFORMAT, NOINIT, NAME = 'db_ssc_2222-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10\"\n\n#################################################\n#\n# Microsoft SQL DB \ubcf5\uc6d0\n#\n#################################################\n\ubc31\uc5c5 \ubcf5\uc6d0\uc740 SSMS(SQL Server Management Studio)\uc5d0\uc11c \uc9c4\ud589\n\n\u203bWAS \uc2dc\uc791 \uc804 JDK 11\uc5d0\uc11c &lt;JDK Dir>\/conf\/security\/java.security\uc758 \uc544\ub798 \ubd80\ubd84\uc744 \ud3b8\uc9d1\ud55c\ub2e4.\n  jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA, \\\n      DH keySize &lt; 1024, EC keySize &lt; 224, 3DES_EDE_CBC, anon, NULL, \\\n      include jdk.disabled.namedCurves\n  \ubd80\ubd84\uc5d0\uc11c TLSv1\uacfc TLSv1.1 \ud56d\ubaa9\uc744 \uc81c\uac70\ud55c\ub2e4.\n\n1. \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc0dd\uc131\n  \u2460\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\ub370\uc774\ud130\ubca0\uc774\uc2a4 > [\u2196\ud31d\uc5c5\uba54\ub274] > \uc0c8 \ub370\uc774\ud130\ubca0\uc774\uc2a4\n  \u2461\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\uc77c\ubc18 > \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc774\ub984(N): db_ssc_2222\n  \u2462\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\uc635\uc158 > \ub370\uc774\ud130 \uc815\ub82c(C): Korean_Wansung_CS_AS\n  \u2463\"\ud655\uc778\" \ubc84\ud2bc\uc744 \ud074\ub9ad\ud558\uc5ec \uc0dd\uc131 \uc9c4\ud589\n\n2. \ubc31\uc5c5 \ubcf5\uc6d0\n  \u25ce\ubc31\uc5c5 \ubcf5\uc6d0 \uc804\n    -\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\ub370\uc774\ud130\ubca0\uc774\uc2a4 > db_ssc_2222 > [\u2196\ud31d\uc5c5\uba54\ub274] > \ud0dc\uc2a4\ud06c > \uc624\ud504\ub77c\uc778 \uc0c1\ud0dc\ub85c \ub9cc\ub4e4\uae30\n  \u25ce\ubcf5\uc6d0 \uc9c4\ud589\n    \u2460\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\ub370\uc774\ud130\ubca0\uc774\uc2a4 > db_ssc_2222 > [\u2196\ud31d\uc5c5\uba54\ub274] > \ud0dc\uc2a4\ud06c > \ubcf5\uc6d0 > \ub370\uc774\ud130\ubca0\uc774\uc2a4\n    \u2461\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\uc77c\ubc18 > \u2611\uc7a5\uce58 > BAK\ud30c\uc77c \ucd94\uac00 : \ubcf5\uc6d0\ud560 \ubc31\uc5c5 \uc138\ud2b8\uc5d0 \ud574\ub2f9 \ubc31\uc5c5\uc774 \ud45c\uc2dc\ub418\uc5b4\uc57c \ud568.\n    \u2462\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\uc77c\ubc18 > \ub300\uc0c1 > \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc120\ud0dd\n    \u2463\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\uc635\uc158 > \u2611\uae30\uc874 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ub36e\uc5b4\uc4f0\uae30, \u2611\ub300\uc0c1 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0 \ub300\ud55c \uae30\uc874 \uc5f0\uacb0 \ub2eb\uae30\n    \u2464\"\ud655\uc778\" \ubc84\ud2bc\uc744 \ud074\ub9ad\ud558\uc5ec \ubc31\uc5c5 \ubcf5\uc6d0 \uc9c4\ud589\n\n3. \uc0ac\uc6a9\uc790 \ucd94\uac00\n  \u2460\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\ubcf4\uc548 > \ub85c\uadf8\uc778 > [\u2196\ud31d\uc5c5\uba54\ub274] > \uc0c8 \ub85c\uadf8\uc778\n  \u2461\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\uc77c\ubc18 > \ub85c\uadf8\uc778 \uc774\ub984(N) : u_ssc\n  \u2462\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\uc77c\ubc18 > \u2611SQL Server \uc778\uc99d(S) > &lt;PASSWORD> \uc785\ub825\n  \u2463\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\uc77c\ubc18 > \u25a1\ub2e4\uc74c \ub85c\uadf8\uc778\ud560 \ub54c \ubc18\ub4dc\uc2dc \uc554\ud638 \ubcc0\uacbd\n  \u2464\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\uc11c\ubc84 \uc5ed\ud560 > \u2611public\n  \u2465\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\uc0ac\uc6a9\uc790 \ub9e4\ud551 > \uc774 \ub85c\uadf8\uc778\uc73c\ub85c \ub9e4\ud551\ub41c \uc0ac\uc6a9\uc790 > \u2611db_ssc_2222\n  \u2466\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\uc0ac\uc6a9\uc790 \ub9e4\ud551 > \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc5ed\ud560 \uba64\ubc84 \uc790\uaca9: \u2611db_owner, \u2611public\n  \u2467\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\uc0c1\ud0dc > \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc5d4\uc9c4 \uc5f0\uacb0 \uad8c\ud55c > \u2611\ud5c8\uc6a9\n  \u2468\uc88c\uce21\ud2b8\ub9ac\uba54\ub274-\uc0c1\ud0dc > \ub85c\uadf8\uc778 > \u2611\ud5c8\uc6a9\n  \u203b\uc624\ub958\uac00 \ubc1c\uc0dd\ud560 \uacbd\uc6b0\n    USE db_ssc_2222;\n    EXEC sp_change_users_login 'Auto_Fix', 'u_ssc';\n\nREM ---------------------------------------------\n\n#################################################\n#\n# MySQL \ubc31\uc5c5\n#\n#################################################\nMKDIR C:\\database\\backup\\mysql\nmysqldump --single-transaction --routines --triggers -u root -p&lt;PASSWORD> db_ssc_2222 > C:\\database\\backup\\mysql\\db_ssc_2222.sql\n\n#################################################\n#\n# MySQL \ubcf5\uc6d0\n#\n#################################################\nmysql -u root -p&lt;PASSWORD>\n  -- DB\uc640 \uc0ac\uc6a9\uc790\ub97c \uc0ad\uc81c\ud55c\ub2e4.\n  DROP DATABASE IF EXISTS db_ssc_2222;\n  DROP USER IF EXISTS 'u_ssc'@'localhost';\n  DROP USER IF EXISTS 'u_ssc'@'%';\n  -- DB\uc640 \uc0ac\uc6a9\uc790\ub97c \uc0dd\uc131\ud55c\ub2e4.\n  CREATE DATABASE db_ssc_2222 CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;\n  CREATE USER 'u_ssc'@'localhost' IDENTIFIED BY '&lt;PASSWORD>';\n  CREATE USER 'u_ssc'@'%' IDENTIFIED BY '&lt;PASSWORD>';\n  GRANT ALL PRIVILEGES ON db_ssc_2222.* TO 'u_ssc'@'localhost';\n  GRANT ALL PRIVILEGES ON db_ssc_2222.* TO 'u_ssc'@'%';\n\n# \ubcf5\uc6d0\nmysql -u root -p&lt;PASSWORD> db_ssc_2222 &lt; C:\\database\\backup\\mysql\\db_ssc_2222.sql<\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[66,10],"tags":[],"class_list":["post-5818","post","type-post","status-publish","format-standard","hentry","category-computing_fortify","category-computing_database"],"_links":{"self":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/5818","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5818"}],"version-history":[{"count":0,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/5818\/revisions"}],"wp:attachment":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5818"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5818"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5818"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}