oracle 数据库archive归档及flashback 闪回

smg2117

<P>理解几个参数:<BR>log_archive_dest_n和log_archive_dest不能共存<BR>1、log_archive_dest_n 需要加上location/service参数的，能实现归档到standby 数据库，实现 dataguard。<BR>2、log_archive_dest 与 log_archive_dest_n 及 USE_DB_RECOVERY_FILE_DEST 互斥，不能同时设置。<BR>3、log_archive_start 在9I 之前的版本，需要设置 数据库启动时，才能自动归档！<BR>&nbsp;<BR>&nbsp;<BR>-------------------------------------------－－－－－<BR>实例一：开归档实例：（没有设置任何归档路径的情况下)<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>实际上，oracle是通过隐式的设置log_archive_dest_10='location= USE_DB_RECOVERY_FILE_DEST'来实现的。<BR>所以，如果之后你修改过log_archive_dest_n将归档日志保存到其他位置，也可以修改该参数继续使用闪回恢复区。<BR>&nbsp;<BR>注意问题：<BR>1、必须先启用归档才能，启用 flashback 闪回<BR>当归档路径为 USE_DB_RECOVERY_FILE_DEST时，否则会报错：<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>此时归档路径变为standby_archive_dest<BR>alter system set log_archive_dest='/arch' scope=both;<BR>archive log list 显示归档路径为 /arch<BR>&nbsp;<BR>3、当归档路径设置为 Archive destination&nbsp; USE_DB_RECOVERY_FILE_DEST时，要改变归档路径，可以<BR>alter system set log_archive_dest_1='location=/arch/' scope=both;<BR>此时 archive log list 显示归档路径为 /arch<BR>&nbsp;<BR>4、当归档路径设置为 Archive destination&nbsp; USE_DB_RECOVERY_FILE_DEST时，要改变归档路径为log_archive_dest.<BR>这是必须启动数据库到 mount 状态 ：alter database&nbsp; flashback off;<BR>alter system set db_recovery_file_dest='' scope=both;<BR>此时 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>实例二：　取消归档，重新设置归档路径<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>现在归档路径变为 ： 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>现在归档路径变为 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>