Oracle Flashback 闪回功能的设置和使用

最新推荐文章于 2024-05-07 14:47:00 发布

原创最新推荐文章于 2024-05-07 14:47:00 发布 · 887 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

本文详细介绍了如何在Oracle数据库中配置和使用Flashback功能，包括检查Flashback状态、调整归档日志模式、设置归档路径及大小、激活Flashback功能，并提供了关键SQL命令示例。

1. checking the flashback status
SQL> select name,flashback_on from v$database;

NAME	FLASHBACK_ON
ORCL	NO

now the status of Flashback is off.

2. checking the database archive status
SQL> archive log list;


Database log mode	No Archive Mode
Automatic archival	Disabled
Archive destination	USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence	983
Current log sequence	985

3. checking the database archive path and archive size
SQL> show parameter db_recovery_file_dest_size;
SQL> show parameter db_recovery_file_dest;

NAME	TYPE	VALUE
db_recovery_file_dest	string	/u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size	big integer	4560M

4. the database must be archivelog mode while using the flashback

changing the database into archivelog mode
SQL> conn / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 16 11:17:30 2019
Copyright © 1982, 2014, Oracle. All rights reserved.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 629145600 bytes
Fixed Size 2927528 bytes
Variable Size 268436568 bytes
Database Buffers 352321536 bytes
Redo Buffers 5459968 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter system archive log start;
System altered.
SQL> exit


closing & starting archivelog mode while database running	– ARCHIVE LOG STOP --ARCHIVE LOG START
archive manually	LOG_ARCHIVE_START=FALSE
archive all log files	ALTER SYSTEM ARCHIVE LOG ALL;
changing archive file path	ALTER SYSTEM ARCHIVE LOG CURRENT TO ‘&PATH’;
using multiple archive process	ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=10;
checking all archive process	SELECT * FROM V$BGPROCESS; --SELECT * FROM V$ARCHIVE_PROCESSES;
local archive target	LOG_ARCHIVE_DEST_1=“LOCATION=&PATH”;
remote archive target	LOG_ARCHIVE_DEST_2=“SERVICE=STANDBY_DB1”;
getting archive log info	–V$ARCHIVED_LOG --V$ARCHIVE_DEST --V$LOG_HISTORY --V$DATABASE --V$ARCHIVE_PROCESSES --ARCHIVE LOG LIST;

5. activate flashback
SQL> alter database flashback on;
(
disactivate flashback with
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback off;
)

checking the status of flashback
SQL> select name,flashback_on from v$database;

NAME	FLASHBACK_ON
ORCL	YES

checking the recovery settings
SQL> show parameter db_recovery;

NAME	TYPE	VALUE
db_recovery_file_dest	string	/u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size	big integer	4560M

setting the recovery size
SQL> alter system set db_recovery_file_dest_size=‘2G’;

setting the recovery path
alter system set db_recovery_file_dest=’$recovery_path’;
setting the flashback max time
SQL> show parameter flashback_retention_target;