oracle 數(shù)據(jù)庫archive歸檔及flashback 閃回

smg2117

<P>理解幾個(gè)參數(shù):<BR>log_archive_dest_n和log_archive_dest不能共存<BR>1、log_archive_dest_n 需要加上location/service參數(shù)的，能實(shí)現(xiàn)歸檔到standby 數(shù)據(jù)庫，實(shí)現(xiàn) dataguard。<BR>2、log_archive_dest 與 log_archive_dest_n 及 USE_DB_RECOVERY_FILE_DEST 互斥，不能同時(shí)設(shè)置。<BR>3、log_archive_start 在9I 之前的版本，需要設(shè)置 數(shù)據(jù)庫啟動(dòng)時(shí)，才能自動(dòng)歸檔！<BR>&nbsp;<BR>&nbsp;<BR>-------------------------------------------－－－－－<BR>實(shí)例一：開歸檔實(shí)例：（沒有設(shè)置任何歸檔路徑的情況下)<BR>SQL&gt; shutdown immediate<BR>SQL&gt; startup mount<BR>ORACLE instance started.<BR>Total System Global Area&nbsp; 167772160 bytes<BR>Fixed Size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1218316 bytes<BR>Variable Size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 92276980 bytes<BR>Database Buffers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 71303168 bytes<BR>Redo Buffers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2973696 bytes<BR>Database mounte<BR>SQL&gt; alter database archivelog;<BR>SQL&gt; alter system archive log start;<BR>SQL&gt; alter database open;<BR>SQL&gt; archive log list;<BR>Database log mode&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Archive Mode<BR>Automatic archival&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Enabled<BR>Archive destination&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; USE_DB_RECOVERY_FILE_DEST<BR>Oldest online log sequence&nbsp;&nbsp;&nbsp;&nbsp; 60<BR>Next log sequence to archive&nbsp;&nbsp; 62<BR>Current log sequence&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 62<BR>SQL&gt; alter system switch logfile;<BR>&nbsp;<BR>實(shí)際上，oracle是通過隱式的設(shè)置log_archive_dest_10='location= USE_DB_RECOVERY_FILE_DEST'來實(shí)現(xiàn)的。<BR>所以，如果之后你修改過log_archive_dest_n將歸檔日志保存到其他位置，也可以修改該參數(shù)繼續(xù)使用閃回恢復(fù)區(qū)。<BR>&nbsp;<BR>注意問題：<BR>1、必須先啟用歸檔才能，啟用 flashback 閃回<BR>當(dāng)歸檔路徑為 USE_DB_RECOVERY_FILE_DEST時(shí)，否則會(huì)報(bào)錯(cuò)：<BR>alter database flashback on<BR>*<BR>ERROR at line 1:<BR>ORA-38706: Cannot turn on FLASHBACK DATABASE logging.<BR>ORA-38707: Media recovery is not enabled.</P>
<P>2、要更改 log_archive_dest歸檔路徑<BR>先停止 alter database flashback off;<BR>alter system set db_recovery_file_dest='' scope=both;<BR>此時(shí)歸檔路徑變?yōu)閟tandby_archive_dest<BR>alter system set log_archive_dest='/arch' scope=both;<BR>archive log list 顯示歸檔路徑為 /arch<BR>&nbsp;<BR>3、當(dāng)歸檔路徑設(shè)置為 Archive destination&nbsp; USE_DB_RECOVERY_FILE_DEST時(shí)，要改變歸檔路徑，可以<BR>alter system set log_archive_dest_1='location=/arch/' scope=both;<BR>此時(shí) archive log list 顯示歸檔路徑為 /arch<BR>&nbsp;<BR>4、當(dāng)歸檔路徑設(shè)置為 Archive destination&nbsp; USE_DB_RECOVERY_FILE_DEST時(shí)，要改變歸檔路徑為log_archive_dest.<BR>這是必須啟動(dòng)數(shù)據(jù)庫到 mount 狀態(tài) ：alter database&nbsp; flashback off;<BR>alter system set db_recovery_file_dest='' scope=both;<BR>此時(shí) archive log list&nbsp; 顯示歸檔路徑為standby_archive_dest.<BR>alter system set log_archive_dest='/arch/' scope=both;<BR>alter system set db_recovery_file_dest='/oracle/flash_recovery_area/' scope=both;<BR>alter system <BR>alter database flashback on;<BR>alter database open;<BR>SQL&gt; alter system archive log start;<BR>System altered.<BR>SQL&gt; alter system switch logfile</P>
<P>--------------－－－－－－－－－－－－－－－－－－－－－－－<BR>實(shí)例二：　取消歸檔，重新設(shè)置歸檔路徑<BR>SQL&gt; shutdown immediate<BR>Database closed.<BR>Database dismounted.<BR>ORACLE instance shut down.<BR>SQL&gt; startup mount<BR>ORACLE instance started.<BR>Total System Global Area&nbsp; 167772160 bytes<BR>Fixed Size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1218316 bytes<BR>Variable Size&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 96471284 bytes<BR>Database Buffers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 67108864 bytes<BR>Redo Buffers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2973696 bytes<BR>Database mounted.<BR>SQL&gt; alter database noarchivelog; 　　<BR>Database altered.<BR>SQL&gt; archive log list<BR>Database log mode&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; No Archive Mode<BR>Automatic archival&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Disabled<BR>Archive destination&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; USE_DB_RECOVERY_FILE_DEST<BR>Oldest online log sequence&nbsp;&nbsp;&nbsp;&nbsp; 61<BR>Current log sequence&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 63<BR>SQL&gt; alter system set log_archive_dest="/arch/" scope=both;<BR>alter system set log_archive_dest="/arch/" scope=both<BR>*<BR>ERROR at line 1:<BR>ORA-02097: parameter cannot be modified because specified value is invalid<BR>ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or<BR>DB_RECOVERY_FILE_DEST<BR>SQL&gt; alter database flashback off; <BR>Database altered.<BR>SQL&gt; alter system set db_recovery_file_dest=''; <BR>System altered.</P>
<P>SQL&gt; archive log list<BR>Database log mode&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; No Archive Mode<BR>Automatic archival&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Disabled<BR>Archive destination&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ?/dbs/arch<BR>Oldest online log sequence&nbsp;&nbsp;&nbsp;&nbsp; 61<BR>Current log sequence&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 63<BR>------------------<BR>現(xiàn)在歸檔路徑變?yōu)?： standby_archive_dest <BR>standby_archive_dest&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; string&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ?/dbs/arch<BR>SQL&gt; alter system set log_archive_dest='/arch/' scope=both;<BR>System altered.</P>
<P>SQL&gt; archive log list;<BR>Database log mode&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; No Archive Mode<BR>Automatic archival&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Disabled<BR>Archive destination&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; /arch/<BR>Oldest online log sequence&nbsp;&nbsp;&nbsp;&nbsp; 61<BR>Current log sequence&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 63<BR>----------------<BR>現(xiàn)在歸檔路徑變?yōu)?log_archive_dest 路徑了<BR>&nbsp;<BR>啟用歸檔<BR>SQL&gt; alter database archivelog;<BR>&nbsp;<BR>啟用flashback<BR>SQL&gt; alter system set db_recovery_file_dest='/oracle/flash_recovery_area' scope=both;<BR>System altered.<BR>SQL&gt; alter database flashback on; <BR>SQL&gt; alter database open;<BR>SQL&gt; select FLAshback_on from v$database;&nbsp;&nbsp;&nbsp; 察看是否開 flashback<BR>FLASHBACK_ON<BR>------------------<BR>YES<BR>SQL&gt; archive log list<BR>Database log mode&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Archive Mode<BR>Automatic archival&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Enabled<BR>Archive destination&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; /arch/<BR>Oldest online log sequence&nbsp;&nbsp;&nbsp;&nbsp; 64<BR>Next log sequence to archive&nbsp;&nbsp; 66<BR>Current log sequence&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 66</P>