oracle db ¹é¾÷(full)
oracle db full backup
backup ¹ÞÀ» µð·ºÅ͸® »ý¼º
# mkdir /home/backup
¹é¾÷¹ÞÀ» µð·ºÅ͸®·Î À̵¿
# cd /home/backup
¹é¾÷ ¹ÞÀ» µð·ºÅ͸®·Î À̵¿ÇÏ¿©¾ß ÇØ´ç µð·ºÅ͸®¿¡ .dmp ÆÄÀÏÀÌ »ý¼ºµÈ´Ù
¿À¶óŬ system °èÁ¤ Æнº¿öµå¸¦ ¸ð¸£´Â »óÅ¿´´Ù. Æнº¿öµå º¯°æ
# su - oracle
$ sqlplus / as sysdba
SQL> conn /as sysdba
SQL> alter user system identified by NEW_PASSWORD;
SQL> alter user system identified by NEW_PASSWORD; // NEW_PASSWORD ºÎºÐ¿¡ »õ·Î ÁöÁ¤ÇÒ Æнº¿öµå ÀÔ·Â, Ư¼ö¹®ÀÚ´Â ¾ÈµÇ´Â °ÍÀ¸·Î È®ÀÎ.
SQL> alter user sys identified by NEW_PASSWORD;
SQL> quit
#### Âü°í »çÇ× ####
sqlplus / as sysdba ·Î ·Î±×ÀÎ ÇÏ´Â °æ¿ì¿¡´Â system °èÁ¤ Æнº¿öµå ÆÄÀÏÀ» »ç¿ëÇϰųª OS ÀÎÁõÀ» »ç¿ëÇÑ´Ù.
################
< ¹é¾÷Çϱâ >
$ exp system/sidae file=/home/backup/backup_20141207.dmp full=Y
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.
exp userid=id/pass file=C:/oracleDump.dmp log=C:/oracleDump.log owner=id
¿É¼Ç ¼³¸í
1. statistics=none
– Åë°èÁ¤º¸°¡ Á¤È®ÇÏÁö ¾ÊÀ» ¶§ ÀͽºÆ÷Æ®¸¦ ½ÃÇàÇÏ¸é¼ ¿À¶óŬÀº ¡°°æ°í¿Í ÇÔ²². ..¡± ¶ó´Â ½ÄÀÇ °æ°í¸¦ Ãâ·ÂÇÑ´Ù. º¸±â ½È´Ù¸é ¿É¼ÇÀ» Ãß°¡ÇÑ´Ù.
2. owner=id
– ÇØ´ç À¯Àú°¡ µé°íÀÖ´Â °´Ã¼¸¦ ´ýÇÁÇÑ´Ù.
– full=Y ¿É¼Ç°ú ÇÔ²² »ç¿ëÇÒ ¼ö ¾ø´Ù.
3. full=Y
– DBÀÇ Àüü ±¸Á¶¸¦ ¹é¾÷ÇÑ´Ù.
– owner ¿É¼Ç°ú ÇÔ²² »ç¿ëÇÒ ¼ö ¾ø´Ù.
4. tables=tbl1, tbl2, tbl3
– ÁöÁ¤ÇÑ Å×À̺íµé¸¸ µ¥ÀÌÅÍ¿Í ÇÔ²² ´ýÇÁÇÑ´Ù.
– cmd·Î ½ÇÇàÇÒ°æ¿ì ¼¼¹ÌÄݷбîÁö À̸§À¸·Î ÀνÄÇϹǷΠÁ¶½ÉÇÑ´Ù.
5. log
– cmd ¸í·É¾î â¿¡ Ãâ·ÂµÇ´Â ³»¿ªÀ» ÆÄÀÏ·Î ³²±ä´Ù.
imp userid=id/pass file=C:/oracleDump.dmp log=C:/oracleImpo.log fromuser=id touser=id2
¿É¼Ç ¼³¸í
1. fromuser/touser
– fromuser´Â dmp¸¦ ÅëÇÏ¿© ÀͽºÆ÷Æ®½Ã ÇØ´ç °´Ã¼ÀÇ owner¸¦ ¶æÇÑ´Ù.
– touser´Â dmpÆÄÀϷκÎÅÍ ÀÓÆ÷Æ®µÉ °´Ã¼¸¦ °¡Á®°¥ owner¸¦ ¶æÇÑ´Ù.
2. tables=tbl1, tbl2, tbl3
– dmp³»¿¡ Á¸ÀçÇÏ´Â Å×À̺íÁß ÁöÁ¤ÇÑ Å×À̺íµé¸¸ ã¾Æ¼ µ¥ÀÌÅÍ¿Í ÇÔ²² ÀÓÆ÷Æ®ÇÑ´Ù.
– cmd·Î ½ÇÇàÇÒ°æ¿ì ¼¼¹ÌÄݷбîÁö À̸§À¸·Î ÀνÄÇϹǷΠÁ¶½ÉÇÑ´Ù.
3. log
– cmd ¸í·É¾î â¿¡ Ãâ·ÂµÇ´Â ³»¿ªÀ» ÆÄÀÏ·Î ³²±ä´Ù.
¿ø°ÝÀ¸·Î ÇÏ´Â °æ¿ì
¿ø°Ý ¹é¾÷/º¹¿øÀÇ °æ¿ì tns¿¡ DBLink¸¦ Ãß°¡ÇÏ¿©¾ßÇÑ´Ù.
DBLinkÃß°¡ÇÏ´Â ¹ý
ÇÊÀÚÀÇ tnsÆÄÀÏ °æ·Î´Â ´ÙÀ½°ú °°´Ù.
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora
ȯ°æ¸¶´Ù, ¹öÀü¸¶´Ù ´Ù¸£°ÚÁö¸¸ ±Ã±ØÀûÀ¸·Î 10g À̻󿡼´Â networkÆú´õ ³»¿¡ ÀÖ´Â tnsnames.ora ¸¦ ãÀ¸¸é µÈ´Ù. ´Ü, sample Æú´õ ¾È¿¡ÀÖ´Â °ÍÀº ÇØ´çÇÏÁö ¾Ê´Â´Ù.
¿¾îº¸¸é ´ÙÀ½°ú °°Àº ºÎºÐÀÌ Àִµ¥ ÇØ´ç ºÎºÐÀ» º¹»çÇÏ¿© ¾Æ·¡ÂÊ¿¡ Ãß°¡ÇÑ´Ù.
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
XE¸¦ TNS2¶ó°í ÁöÁ¤ÇÏ°í ÇØ´ç Á¢¼Ó Á¤º¸¸¦ º¯°æÇϵµ·Ï ÇÏ°Ú´Ù.
TNS2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
±×·¯¸é tnsnames.ora³»¿ëÀÌ ´ÙÀ½°ú °°Àº ÇüÅ·Π¼³Á¤µÉ °Í ÀÌ´Ù. (Ãß°¡¸¸ ÇßÀ¸¹Ç·Î ÀÚ½ÅÀÇ È¯°æ¿¡ ¸Â´Â ¼³Á¤Àº ±×´ë·Î µÎ¸é µÈ´Ù.)
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
TNS2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
.
.
.
ÀúÀåÇÑ ÈÄ ¿ø°ÝÁ¢¼ÓÀÌ µÇ´ÂÁö cmd¸¦ ÅëÇØ È®ÀÎÇÑ´Ù.
cmd> sqlplus id/pass@TNS2
select * from user_tables;
Á¤»óÀûÀ¸·Î Á¢¼ÓµÇ´Â °ÍÀÌ È®ÀεǸé exp/imp¸¦ ½ÇÇàÇÒ ¼ö ÀÖ´Ù.
dmp userid=id/pass@TNS2 file=C:/oracleDump.dmp log=C:/oracleDump.log owner=id
imp userid=id/pass@TNS2 file=C:/oracleDump.dmp log=C:/oracleImpo.log fromuser=id touser=id2
Ãâó: http://kkamagistory.tistory.com/373?category=620111 [Karas.]