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