{"id":2693,"date":"2018-02-02T22:00:51","date_gmt":"2018-02-02T13:00:51","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2693"},"modified":"2018-02-02T22:00:51","modified_gmt":"2018-02-02T13:00:51","slug":"ora-1118-%ec%a1%b0%ec%b9%98%eb%b0%a9%eb%b2%95-maxdatafiles-%ec%99%80-db_files","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2693","title":{"rendered":"ORA-1118 \uc870\uce58\ubc29\ubc95 ( MAXDATAFILES \uc640 DB_FILES )"},"content":{"rendered":"
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n

\ud14c\uc774\ube14 \uc2a4\ud398\uc774\uc2a4\ub97c \ub9cc\ub4e4\uac70\ub098 \ub370\uc774\ud0c0 \ud654\uc77c\uc744 \ucd94\uac00\ud558\ub2e4 \ubcf4\uba74<\/p>\n

 <\/p>\n

ORA-01118: cannot add any more database files: limit of XXX exceeded<\/p>\n

 <\/p>\n

\uc640 \uac19\uc740 \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud558\ub294 \uacbd\uc6b0\uac00 \uc788\ub2e4.<\/p>\n

 <\/p>\n

\uc624\ub77c\ud074\uc758 \ub370\uc774\ud0c0 \ud654\uc77c\uc758 \ucd5c\ub300 \uac2f\uc218\ub294 MAXDATAFILES\uc640 DB_FILES \uc5d0 \uc758\ud574\uc11c \uc81c\ud55c\uc744 \ubc1b\ub294\ub370 \uc5ec\uae30\uc11c\ub294 \uc774 \uc5d0\ub7ec\ub97c \ud574\uacb0\ud558\ub294 \ubc29\ubc95\uc744 \uc54c\uc544\ubcf4\uae30\ub85c \ud55c\ub2e4.<\/p>\n

 <\/p>\n

\ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub97c \ucc98\uc74c \ub9cc\ub4e4 \ub54c \uc0ac\uc6a9\ub418\ub294 CREATE DATABASE \uba85\ub839\uc5d0\uc11c MAXDATAFILES \ub77c\ub294 \ud30c\ub77c\ubbf8\ud130\ub97c \ucc3e\uc544\ubcfc \uc218 \uc788\ub2e4.<\/p>\n

 <\/p>\n

\uc5ec\uae30\uc11c \uc9c0\uc815\ub41c \uac12(\uba85\uc2dc\ub97c \uc548\ud558\uba74 \ub514\ud3f4\ud2b8\ub85c \uc124\uc815)\uc740 \ucf58\ud2b8\ub864 \ud654\uc77c\uc5d0 \uae30\ub85d\ub41c\ub2e4.<\/p>\n

 <\/p>\n

\uc774 \uac12\uc740 \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0 \ub300\ud574\uc11c \uc124\uc815\ub41c \ucd5c\ub300 \ub370\uc774\ud0c0\ud654\uc77c \uac2f\uc218\uc774\ub2e4. \uc774 \uac12\uc744 \ubcc0\uacbd\ud558\ub824\uba74 \ucf58\ud2b8\ub864 \ud654\uc77c\uc744 \ub2e4\uc2dc \ub9cc\ub4e4\uc5b4\uc57c \ud55c\ub2e4.<\/p>\n

 <\/p>\n

\ud55c\ud3b8, init.ora(UNIX \uc5d0\uc11c\ub294 init.ora, VMS \uc5d0\uc11c\ub294 __init.ora)\uc5d0\ub294 DB_FILES \ub77c\ub294 \ud30c\ub77c\ubbf8\ud130\uac00 \uc788\ub294\ub370 \uc774 \uac12\uc740 \ud574\ub2f9 \uc778\uc2a4\ud134\uc2a4\uc5d0 \ub300\ud574\uc11c \uc9c0\uc815\ub41c \ucd5c\ub300 \ub370\uc774\ud0c0\ud654\uc77c \uac2f\uc218\uc774\ub2e4.<\/p>\n

 <\/p>\n

DB_FILES\ub294 \ub2e8\uc21c\ud788 \uc5d0\ub514\ud130 \uc0c1\uc5d0\uc11c init.ora \ud654\uc77c\uc744 \uc218\uc815\ud55c \ub2e4\uc74c\uc5d0 DB\ub97c Restartup \ud558\uba74 \uc0c8\ub85c\uc6b4 \uac12\uc774 \uc801\uc6a9\ub41c\ub2e4.<\/p>\n

 <\/p>\n

1. \uc65c MAXDATAFILES \uc640 \uac19\uc740 \ud55c\uacc4\uac12\uc744 \uc124\uc815\ud558\ub294\uac00?<\/p>\n

-> O\/S\ub294 \uc624\ub77c\ud074 \ud654\uc77c\uc758 \uac2f\uc218\ub97c \uc9c0\uc815\ud558\uae30 \uc704\ud558\uc5ec \ud2b9\uc815\ud55c \uac2f\uc218 bit\ub97c \uc0ac\uc6a9\ud558\uba70 \uc774 \uac12\uc774 Platform\uc5d0 \ub530\ub77c \ub2e4\ub974\ub2e4. MAXDATAFILES\ub294 \uc774 \uac12\uc758 \uc601\ud5a5 \ud558\uc5d0 \uc788\uac8c \ub41c\ub2e4.<\/span><\/p>\n

 <\/p>\n

\uc77c\ubc18\uc801\uc778 \ucd5c\ub300 \uac12\uc740 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

UNIX 62 1022<\/p>\n

VMS 254 1022<\/p>\n

DOS 254 NA<\/p>\n

[\ucc38\uc870]\uc77c\ubd80 \uc720\ub2c9\uc2a4 Platform\uc0c1\uc758 \uc774\uc804 \ubc84\uc804\uc758 \uacbd\uc6b0 1022\ubcf4\ub2e4 \uc791\uc740 \uacbd\uc6b0\ub3c4 \uc788\ub2e4.<\/p>\n

 <\/p>\n

2. \uc65c MAXDATAFILES \uac12\uc744 \uac00\ub2a5\ud55c \ucd5c\ub300\ub85c \uc124\uc815\ud574 \ub450\uc9c0 \uc54a\ub294\uac00?<\/p>\n

-> MAXDATAFILES \ub97c \ud06c\uac8c \uc9c0\uc815\ud558\uba74 \uadf8\ub9cc\ud07c \ucf58\ud2b8\ub864 \ud654\uc77c\uc758 \ud06c\uae30\ub3c4 \ub298\uc5b4\ub098\uae30 \ub54c\ubb38\uc774\ub2e4.<\/p>\n

 <\/p>\n

3. \uc65c DB_FILES\ub97c MAXDATAFILES \uc758 \ud06c\uae30\ub9cc\ud07c \ud06c\uac8c \uc124\uc815\ud574 \ub450\uc9c0 \uc54a\ub294\uac00?<\/p>\n

-> DB_FILES\ub97c \ub298\ub9ac\uba74 \uac01 User Process \uc5d0 \ud560\ub2f9\ub418\ub294 PGA(Program Global Area) \uc758 \ud06c\uae30\uac00 \ucee4\uc9c0\uae30 \ub54c\ubb38\uc774\ub2e4.<\/p>\n

 <\/p>\n

4. \uc0ac\uc6a9\ud558\ub294 \uc2dc\uc2a4\ud15c MAXDATAFILES\ub97c \uc54c \uc218 \uc788\ub294 \ubc29\ubc95\uc740?<\/p>\n

-> \uc2dc\uc2a4\ud15c\uc5d0 \ub530\ub77c\uc11c \ub2e4\ub974\ubbc0\ub85c Installation Guide \ub97c \ucc38\uc870\ud574\uc57c\ub9cc \ud55c\ub2e4.<\/p>\n

 <\/p>\n

5. \ucf58\ud2b8\ub864 \ud654\uc77c\uc758 \uc704\uce58\ub97c \uc544\ub294 \ubc29\ubc95\uc740?<\/p>\n

-> SQL>show parameter control_files;<\/p>\n

\ub610\ub294<\/p>\n

SQL>select * from v$controlfile; (7.0.16 \uc774\uc0c1)<\/p>\n

\uc704\uc758 Query \ub97c \uc774\uc6a9\ud558\uac70\ub098 $ORACLE_HOME\/dbs\/config.ora \ud654\uc77c\uc744 \ubcf4\uba74 \uc54c \uc218 \uc788\ub2e4.<\/p>\n

 <\/p>\n

\n

[ORA-1118 \ud574\uacb0\ubc29\ubc95]<\/p>\n

-> ORA-1118 \uc5d0\ub7ec\ub294 \ub370\uc774\ud0c0 \ud654\uc77c\uc758 \uac2f\uc218\uac00 MAXDATAFILES \uac12\uc5d0 \ub3c4\ub2ec\ud55c \uacbd\uc6b0 \ubc1c\uc0dd\ud55c\ub2e4. DB_FILES \uac12\uc5d0 \ub3c4\ub2ec\ud55c \uacbd\uc6b0\ub77c\uba74 ORA-59 \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud55c\ub2e4. ORA-59 \uc5d0\ub7ec\ub294 init.ora \uc758 DB_FILES \ub97c \ub298\ub824\uc8fc\uace0 DB \ub97c Restartup \ud558\uba74 \ud574\uacb0 \ub418\uc9c0\ub9cc ORA-1118 \uc5d0\ub7ec\ub294 \uc774\uc640\uac19\uc774 \uac04\ub2e8\ud558\uac8c \ud574\uacb0\ub418\uc9c0\ub294 \uc54a\ub294\ub2e4. \ub2e4\uc74c\uacfc \uac19\uc740 \ubc29\ubc95\uc774 \uc788\ub2e4.<\/p>\n

1. \uc5ec\ub7ec\uac1c\uc758 \ub370\uc774\ud0c0\ud654\uc77c\ub85c \uad6c\uc131\ub41c \ud14c\uc774\ube14 \uc2a4\ud398\uc774\uc2a4\uac00 \uc788\uc73c\uba74 \uc774\ub97c Export \ubc1b\uace0 \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4\ub97c Drop\ud55c \ub2e4\uc74c \ud558 \uc758 \ud070 \ub370\uc774\ud0c0\ud654\uc77c\uc744 \uac16\ub3c4\ub85d \ud14c\uc774\ube14 \uc2a4\ud398\uc774\uc2a4\ub97c \ub9cc\ub4e4\uace0 Import\ub97c \ud55c\ub2e4.<\/p>\n

2.\u00a0MAXDATAFILES\ub97c \ub298\ub9ac\uae30 \uc704\ud574\uc11c\ub294 DB\ub97c \uc0c8\ub85c \ub9cc\ub4e4\uc5b4\uc57c \ud558\uba70 \uadf8 \uc774\ud6c4 \ubc84\uc83c\uc744 \uc0ac\uc6a9\uc911\uc774\ub77c\uba74 \ucf58\ud2b8\ub864 \ud654\uc77c\uc744 \uc0c8\ub85c \ub9cc\ub4e4\uc5b4\uc11c MAXDATAFILES\ub97c \ub298\ub9b4 \uc218 \uc788\ub2e4.<\/p>\n

 <\/p>\n

 <\/p>\n

1. DB\uac00 mount \ub610\ub294 open \ub41c \uc0c1\ud0dc\uc5d0\uc11c<\/p>\n

SQL>alter database backup controlfile to trace;<\/p>\n

\uc640 \uac19\uc740 \uba85\ub839\uc744 \ub0b4\ub9ac\uba74 \uc5d0 \uc9c0\uc815\ub41c \ub514\ub809\ud1a0\ub9ac – ORACLE_HOME\/rdbms\/log – \uc5d0 \ud2b8\ub808\uc774\uc2a4 \ud654\uc77c\uc774 \ud558\ub098 \uc0dd\uae34\ub2e4. \uc774 \ud654\uc77c \ucc3e\uc73c\ub824\uba74 \ud574\ub2f9 \ub514\ub809\ud1a0\ub9ac\uc5d0\uc11c \uac00\uc7a5 \ucd5c\uadfc\uc5d0 \uc0dd\uae34 \ud2b8\ub808\uc774\uc2a4 \ud654\uc77c\uc744 \ucc3e\uc73c\uba74 \ub41c\ub2e4.<\/p>\n

\uc774 \ud654\uc77c\uc744 \ub2e4\ub978 \uc774\ub984\uc73c\ub85c \ubcf5\uc0ac\ud55c \ub2e4\uc74c \uc5d0\ub514\ud130\ub85c \uc5f4\uc5b4\uc11c CREATE CONTROLFILE \uba85\ub839 \ubd80\ubd84 \uc678\uc758 \ubd88\ud544\uc694\ud55c \ubd80\ubd84\uacfc ‘REUSE’ OPTION \uc744 \uc9c0\uc6b0\uace0 MAXDATAFILES\ub97c \ub298\ub824 \uc900\ub2e4. \uc774 \ud654\uc77c\uc758 \uc774\ub984\uc744 newctl.sql \ub85c \ud558\uae30\ub85c \ud55c\ub2e4.<\/p>\n

 <\/p>\n

2. ORCACLE_HOME\/dbs \uc5d0 \uc788\ub294 Control file \uc744 \ub2e4\ub978 \uc774\ub984\uc73c\ub85c MOVE \ud55c\ub2e4.<\/p>\n

 <\/p>\n

3. DB\ub97c NORMAL \ub610\ub294 IMMEDIATE\ub85c Shutdown \ud558\uace0, \ucf58\ud2b8\ub864 \ud654\uc77c \uc0dd\uc131\uc2dc DB \uc601\ud5a5\uc744 \uc904 \uc218 \uc788\uae30 \ub54c\ubb38\uc5d0 \ub9cc\uc57d\uc744 \uc704\ud574\uc11c DB \uc804\uccb4\ub97c \ubc31\uc5c5 \ubc1b\ub3c4\ub85d \ud55c\ub2e4.<\/p>\n

 <\/p>\n

4. \ud604\uc7ac \uc0ac\uc6a9\uc911\uc778 \ucf58 \ub864 \ud654\uc77c\uc744 \ub2e4\ub978 \uc774\ub984\uc73c\ub85c \uc62e\uae30\uace0 \ub2e4\uc74c\uc744 \uc2e4\ud589\ud55c\ub2e4.<\/p>\n

SQL>startup nomount<\/p>\n

SQL>@newctl<\/p>\n

SQL>alter database open;<\/p>\n

 <\/p>\n

5. \uc774\uc81c \ud544\uc694\ud55c \ubaa8\ub4e0 \uc791\uc5c5\uc740 \ub05d\ub0ac\uc9c0\ub9cc \uc5ec\uae30\uc11c \ub2e4\uc2dc \ud55c\ubc88 Full Bakcup \uc744 \ubc1b\ub294 \uac83\uc774 \uc88b\ub2e4.<\/p>\n

 <\/p>\n

[ \ucc38\uace0 ] \ub2e4\uc74c\uc740 \ucf58\ud2b8\ub864 \ud654\uc77c\uc744 \uc0dd\uc131\ud558\ub294 \uba85\ub839\uc758 \uc608\uc774\ub2e4.<\/p>\n

SQL> STARTUP NOMOUNT<\/p>\n

SQL> CREATE CONTROLFILE REUSE<\/p>\n

DATABASE ORACLE<\/p>\n

LOGFILE ‘\/users\/oracle\/dbs\/log1ORACLE.dbf’,<\/p>\n

‘\/users\/oracle\/dbs\/log2ORACLE.dbf’,<\/p>\n

‘\/users\/oracle\/dbs\/log3ORACLE.dbf’<\/p>\n

NORESETLOGS<\/p>\n

DATAFILE ‘\/users\/oracle\/dbs\/systORACLE.dbf’,<\/p>\n

‘\/users\/oracle\/dbs\/rbsORACLE.dbf’,<\/p>\n

‘\/users\/oracle\/dbs\/tempORACLE.dbf’,<\/p>\n

‘\/users\/oracle\/dbs\/toolORACLE.dbf’,<\/p>\n

‘\/users\/oracle\/dbs\/usrORACLE.dbf’<\/p>\n

MAXDATAFILES 121;<\/p>\n

SQL> ALTER DATABASE OPEN NORESETLOGS;<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

\ud14c\uc774\ube14 \uc2a4\ud398\uc774\uc2a4\ub97c \ub9cc\ub4e4\uac70\ub098 \ub370\uc774\ud0c0 \ud654\uc77c\uc744 \ucd94\uac00\ud558\ub2e4 \ubcf4\uba74   ORA-01118: cannot add any more database files: limit of XXX exceeded   \uc640 \uac19\uc740 \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud558\ub294 \uacbd\uc6b0\uac00 \uc788\ub2e4.   \uc624\ub77c\ud074\uc758 \ub370\uc774\ud0c0 \ud654\uc77c\uc758 \ucd5c\ub300 \uac2f\uc218\ub294 MAXDATAFILES\uc640 DB_FILES \uc5d0 \uc758\ud574\uc11c \uc81c\ud55c\uc744 \ubc1b\ub294\ub370 \uc5ec\uae30\uc11c\ub294 \uc774 \uc5d0\ub7ec\ub97c \ud574\uacb0\ud558\ub294 \ubc29\ubc95\uc744 \uc54c\uc544\ubcf4\uae30\ub85c \ud55c\ub2e4.   \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub97c \ucc98\uc74c \ub9cc\ub4e4 \ub54c \uc0ac\uc6a9\ub418\ub294 CREATE DATABASE \uba85\ub839\uc5d0\uc11c MAXDATAFILES […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[14],"tags":[883,884,882],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2693"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2693"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2693\/revisions"}],"predecessor-version":[{"id":2694,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2693\/revisions\/2694"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2693"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2693"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2693"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}