oracle数据库归档闪回,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