oracle flashback ¼¼術總結
Flashback ¼¼ÊõÊÇÒÔUndo segmentÖеÄÄÚÈÝΪ»ù´¡µÄ£¬ Òò´ËÊÜÏÞÓÚUNDO_RETENTON²ÎÊý¡£ÒªÊ¹ÓÃflashback µÄÌØÐÔ£¬±ØÐëÆôÓÃ×Ô¶¯³·Ïú¹ÜÀí±í¿Õ¼ä¡£
ÔÚOracle 10gÖУ¬ Flash back¼Ò×å·ÖΪÒÔϳÉÔ±£º Flashback Database£¬ Flashback Drop£¬Flashback Query(·ÖFlashback Query,Flashback Version Query£¬ Flashback Transaction Query ÈýÖÖ) ºÍFlashback Table¡£
Ò»£® Flashback Database
Flashback Database ¹¦Äܷdz£ÀàËÆÓëRMANµÄ²»ÍêÈ«»Ö¸´£¬ Ëü¿ÉÒÔ°ÑÕû¸öÊý¾Ý¿â»ØÍ˵½¹ýÈ¥µÄij¸öʱµãµÄ״̬£¬ Õâ¸ö¹¦ÄÜÒÀÀµÓÚFlashback log ÈÕÖ¾¡£ ±ÈRMAN¸ü¿ìËٺ͸ßЧ¡£ Òò´ËFlashback Database ¿ÉÒÔ¿´×÷ÊDz»ÍêÈ«»Ö¸´µÄÌæ´ú¼¼Êõ¡£ µ«ËüÒ²ÓÐijЩÏÞÖÆ£º
1. Flashback Database ²»Äܽâ¾öMedia Failure£¬ ÕâÖÖ´íÎóRMAN»Ö¸´ÈÔÊÇΨһѡÔñ
2. Èç¹ûɾ³ýÁËÊý¾ÝÎļþ»òÕßÀûÓÃShrink¼¼ÊõËõСÊý¾ÝÎļþ´óС£¬Õâʱ²»ÄÜÓÃFlashback Database¼¼Êõ»ØÍ˵½¸Ä±ä֮ǰµÄ״̬£¬Õâʱºò¾Í±ØÐëÏÈÀûÓÃRMAN°Ñɾ³ý֮ǰ»òÕßËõС֮ǰµÄÎļþ±¸·Ýrestore ³öÀ´£¬ È»ºóÀûÓÃFlashback Database Ö´ÐÐʣϵÄFlashback Datbase¡£
3. Èç¹û¿ØÖÆÎļþÊÇ´Ó±¸·ÝÖлָ´³öÀ´µÄ£¬»òÕßÊÇÖؽ¨µÄ¿ØÖÆÎļþ£¬Ò²²»ÄÜʹÓÃFlashback Database¡£
4. ʹÓÃFlashback DatabaseËøÄָܻ´µ½µÄ×îÔçµÄSCN£¬ È¡¾öÓëFlashback LogÖмǼµÄ×îÔçSCN¡£
Flashback Database ¼Ü¹¹
Flashback Database Õû¸ö¼Ü¹¹°üÀ¨Ò»¸ö½ø³ÌRecover Writer(RVWR)ºǫ́½ø³Ì£¬Flashback Database LogÈÕÖ¾ ºÍFlash Recovery Area¡£Ò»µ©Êý¾Ý¿âÆôÓÃÁËFlashback Database£¬ ÔòRVWR½ø³Ì»áÆô¶¯£¬¸Ã½ø³Ì»áÏòFlash Recovery AreaÖÐдÈëFlashback Database Log£¬ ÕâЩÈÕÖ¾°üÀ¨µÄÊÇÊý¾Ý¿éµÄ " Ç°¾µÏñ(before image)"£¬ ÕâÒ²ÊÇFlashback Database ¼¼Êõ²»ÍêÈ«»Ö¸´¿éµÄÔÒò¡£
[oracle@dba ~]$ ps -ef|grep rvw
oracle 12620 12589 0 13:21 pts/1 00:00:00 grep rvw
ÆôÓÃFlashback Database
Êý¾Ý¿âµÄFlashback Database¹¦ÄÜȱʡÊǹرյģ¬ÒªÏëÆôÓÃÕâ¸ö¹¦ÄÜ£¬¾ÍÐèÒª×öÈçÏÂÅäÖá£
1. ÅäÖÃFlash Recovery Area
ÒªÏëʹÓÃFlashback Database£¬ ±ØÐëʹÓÃFlash Recovery Area£¬ÒòΪFlashback Database LogÖ»Äܱ£´æÔÚÕâÀï¡£ ÒªÅäÖõÄ2¸ö²ÎÊýÈçÏ£¬Ò»¸öÊÇ´óС£¬Ò»¸öÊÇλÖá£Èç¹ûÊý¾Ý¿âÊÇRAC£¬flash recovery area ±ØÐëλÓÚ¹²Ïí´æ´¢ÖС£Êý¾Ý¿â±ØÐë´¦ÓÚarchivelog ģʽ.
ÆôÓÃFlash Recovery Area£º
SQL>ALTER SYSTEM SET DB_RECOVERY_F
Ïà¹ØÎĵµ£º
1. select * from t1 left join t2 on t1.c1 = t2.c2
ÊÇ×ó±ßµÄ±í£¨t1£© È«²¿ÏÔʾ£¬t2ûÓеÄÓÃnull´úÌæ¡£ ÓÒÁ¬½ÓÏà·´£¨t2£©
2. £¨+£©µÄÁ¬½ÓʱÁíÒ»¸öÈ«²¿ÏÔʾ¡£
select * from t1 left join t2 on t1.c1 = t2.c2 ºÍ select * from a,b where t1.c1 = t2.c2(+) Ч¹ûÒ»Ñù¡£
3. FULL OUTER JOIN:È«Íâ¹ØÁª
¡¡¡¡SELECT e.last ......
±¸×¢£º¾¹ýÇ°ÆÚµÄlinuxϵͳ»·¾³µÄÅäÖôÍê³É£¬ÏÂÃæ¾Í¿ªÊ¼°²×°oracleÊý¾Ý¿â¡£oralceÊý¾Ý´ó¼ÒÈ¥oracle¹Ù·½ÍøÕ¾ÉÏÏÂÔØlinux»·¾³Ïµİ汾¡£ºÜÒź¶½ØͼÉÏ´«²»ÁË¡£
Èý£®Oracle database°²×°¾ßÌå°²×°²½Öè
<1>´´½¨°²×°oracleĿ¼¼°Ö÷Êôµ÷Õû
[root@mylinux oracle]# mv database/ /u01
[root@mylinux u ......
Centos redhat ,oracle10g,oracle11g¾ùÊÊÓÃ
1. ±àд½Å±¾£º
# vi startoracle.sh
#11gµÄ»°Ö»ÊÇÕâ¸öĿ¼ÓÐËùÇø±ð
ORACLE_HOME=/home/oracle/product/10.2.0/db_1;export ORACLE_HOME
ORACLE_SID=orcl;export ORACLE_SID #ÕâÀïÅäÉÏÄãµÄ±¾µØʾÀýÃû
&nbs ......
ÏÂÔؽâѹÁËOracle SQL Developer¹¤¾ß£¬ÔËÐÐʱ£¬Æô¶¯²»ÁË£¬±¨´íÐÅÏ¢ÈçÏ£º
---------------------------
Unable to create an instance of the Java Virtual Machine
Located at path:
<SQLDEVELOPER>\jdk\jre\bin\client\jvm.dll
---------------------------
ÊÇJVM²ÎÊýÉèÖõÄÎÊÌ⣬ÎҵĽâ¾ö·½°¸ÈçÏ£º
<SQ ......
CHAR ¹Ì¶¨³¤¶È×Ö·û´® ×î´ó³¤¶È2000 bytes
VARCHAR2 ¿É±ä³¤¶ÈµÄ×Ö·û´® ×î´ó³¤¶È4000 bytes ¿É×öË÷ÒýµÄ×î´ó³¤¶È749
NCHAR ¸ù¾Ý×Ö·û¼¯¶ø¶¨µÄ¹Ì¶¨³¤¶È×Ö·û´® ×î´ó³¤¶È2000 bytes
NVARCHAR2 ¸ù¾Ý×Ö·û¼¯¶ø¶¨µÄ¿É±ä³¤¶È×Ö·û´® ×î´ó³¤¶È4000 bytes
DATE ÈÕÆÚ£ ......