Chinaunix

标题: oracle 数据库archive归档及flashback 闪回 [打印本页]

作者: smg2117 时间: 2011-12-21 08:43
标题: oracle 数据库archive归档及flashback 闪回
理解几个参数: log_archive_dest_n和log_archive_dest不能共存 1、log_archive_dest_n 需要加上location/service参数的，能实现归档到standby 数据库，实现 dataguard。 2、log_archive_dest 与 log_archive_dest_n 及 USE_DB_RECOVERY_FILE_DEST 互斥，不能同时设置。 3、log_archive_start 在9I 之前的版本，需要设置数据库启动时，才能自动归档！     -------------------------------------------－－－－－ 实例一：开归档实例：（没有设置任何归档路径的情况下) SQL> shutdown immediate SQL> startup mount ORACLE instance started. Total System Global Area  167772160 bytes Fixed Size                  1218316 bytes Variable Size              92276980 bytes Database Buffers           71303168 bytes Redo Buffers                2973696 bytes Database mounte SQL> alter database archivelog; SQL> alter system archive log start; SQL> alter database open; SQL> archive log list; Database log mode              Archive Mode Automatic archival             Enabled Archive destination            USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     60 Next log sequence to archive   62 Current log sequence           62 SQL> alter system switch logfile;   实际上，oracle是通过隐式的设置log_archive_dest_10='location= USE_DB_RECOVERY_FILE_DEST'来实现的。 所以，如果之后你修改过log_archive_dest_n将归档日志保存到其他位置，也可以修改该参数继续使用闪回恢复区。   注意问题： 1、必须先启用归档才能，启用 flashback 闪回 当归档路径为 USE_DB_RECOVERY_FILE_DEST时，否则会报错： alter database flashback on * ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38707: Media recovery is not enabled.
2、要更改 log_archive_dest归档路径 先停止 alter database flashback off; alter system set db_recovery_file_dest='' scope=both; 此时归档路径变为standby_archive_dest alter system set log_archive_dest='/arch' scope=both; archive log list 显示归档路径为 /arch   3、当归档路径设置为 Archive destination  USE_DB_RECOVERY_FILE_DEST时，要改变归档路径，可以 alter system set log_archive_dest_1='location=/arch/' scope=both; 此时 archive log list 显示归档路径为 /arch   4、当归档路径设置为 Archive destination  USE_DB_RECOVERY_FILE_DEST时，要改变归档路径为log_archive_dest. 这是必须启动数据库到 mount 状态：alter database  flashback off; alter system set db_recovery_file_dest='' scope=both; 此时 archive log list  显示归档路径为standby_archive_dest. alter system set log_archive_dest='/arch/' scope=both; alter system set db_recovery_file_dest='/oracle/flash_recovery_area/' scope=both; alter system alter database flashback on; alter database open; SQL> alter system archive log start; System altered. SQL> alter system switch logfile
--------------－－－－－－－－－－－－－－－－－－－－－－－ 实例二：　取消归档，重新设置归档路径 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area  167772160 bytes Fixed Size                  1218316 bytes Variable Size              96471284 bytes Database Buffers           67108864 bytes Redo Buffers                2973696 bytes Database mounted. SQL> alter database noarchivelog; 　　 Database altered. SQL> archive log list Database log mode              No Archive Mode Automatic archival             Disabled Archive destination            USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     61 Current log sequence           63 SQL> alter system set log_archive_dest="/arch/" scope=both; alter system set log_archive_dest="/arch/" scope=both * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST SQL> alter database flashback off; Database altered. SQL> alter system set db_recovery_file_dest=''; System altered.
SQL> archive log list Database log mode              No Archive Mode Automatic archival             Disabled Archive destination            ?/dbs/arch Oldest online log sequence     61 Current log sequence           63 ------------------ 现在归档路径变为： standby_archive_dest standby_archive_dest                 string      ?/dbs/arch SQL> alter system set log_archive_dest='/arch/' scope=both; System altered.
SQL> archive log list; Database log mode              No Archive Mode Automatic archival             Disabled Archive destination            /arch/ Oldest online log sequence     61 Current log sequence           63 ---------------- 现在归档路径变为 log_archive_dest 路径了   启用归档 SQL> alter database archivelog;   启用flashback SQL> alter system set db_recovery_file_dest='/oracle/flash_recovery_area' scope=both; System altered. SQL> alter database flashback on; SQL> alter database open; SQL> select FLAshback_on from v$database;    察看是否开 flashback FLASHBACK_ON ------------------ YES SQL> archive log list Database log mode              Archive Mode Automatic archival             Enabled Archive destination            /arch/ Oldest online log sequence     64 Next log sequence to archive   66 Current log sequence           66

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)