Oracle production Environment Flash back, archive operation reference

related tests for flashback and archiving Reference blog:http://www.cnblogs.com/hellojesson/p/7050097.html

database management reference in archive Mode blog:http://www.cnblogs.com/hellojesson/p/7182219.html

Scenario Description:

The local database is currently running in non-archive mode and does not have the flashback function turned on!!!

We have to do is to: Database switching in the archive mode, open the database flash back function;

Also: Custom flash recovery area path and size custom archive log storage path and size

Confirm current system environment: no open archive no flash back

SQL> archive log listDatabase log mode           No Archive ModeAutomatic archival           DisabledArchive destination           USE_DB_RECOVERY_FILE_DESTOldest online log sequence     6Current log sequence               8SQL> select flashback_on from v$database;FLASHBACK_ON------------------NO

(1) First set archive log path set archive log storage format

SQL> alter system set log_archive_dest_1=‘location=/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch‘;System altered.SQL> archive log listDatabase log mode           No Archive ModeAutomatic archival           DisabledArchive destination           /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/archOldest online log sequence     6Current log sequence           8

Open Archive Mode

SQL> alter database archivelog;Database altered.

To modify the log file naming format:

SQL> alter system set log_archive_max_processes = 5;SQL> alter system set log_archive_format = "_%t_%s_%r.log" scope=spfile; SQL> archive log listDatabase log mode           Archive ModeAutomatic archival           EnabledArchive destination           /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/archOldest online log sequence     6Next log sequence to archive   8Current log sequence           8

(2) Turn on flash back:

SQL> alter database flashback on;

(3) Then set the flashback path set the flash back path size setting flash back save policy
To set the Flash back zone location:

SQL> alter system set db_recovery_file_dest=‘/home/oracle/u01/app/oracle/account_flashback_area‘ scope=spfile;

OS level Create flashback related directory:

[[email protected] oracle]$ mkdir -p /home/oracle/u01/app/oracle/account_flashback_area

Set the flashback path size

alter system set db_recovery_file_dest_size=5g scope=spfile;

Set the flashback target to 5 days, in minutes, Oracle default 1440 minutes, or one day

SQL> alter system set db_flashback_retention_target=7200 scope=spfile;

SQL> archive log listDatabase log mode           Archive ModeAutomatic archival           EnabledArchive destination           /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/archOldest online log sequence     6Next log sequence to archive   8Current log sequence           8SQL> select open_mode,flashback_on from v$database;OPEN_MODE         FLASHBACK_ON-------------------- ------------------MOUNTED          YES

(4) Restart the database
See if the relevant configuration is in effect

SQL> select open_mode,flashback_on from v$database;OPEN_MODE         FLASHBACK_ON-------------------- ------------------MOUNTED          YESSQL> alter database open;SQL> shutdown immediateSQL> startupSQL> archive log listDatabase log mode           Archive ModeAutomatic archival           EnabledArchive destination           /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/archOldest online log sequence     6Next log sequence to archive   8Current log sequence           8SQL> select open_mode,flashback_on from v$database;OPEN_MODE         FLASHBACK_ON-------------------- ------------------READ WRITE         YES

View the configuration of the Flashback recovery area

SQL> show parameter db_recoveryNAME                     TYPE     VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest             string     /home/oracle/u01/app/oracle/account_flashback_areadb_recovery_file_dest_size         big integer 5G

Check the use of Flash recovery area, view Archivelog

SQL> set linesize 200SQL> set pagesize 80SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;FILE_TYPE         PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES-------------------- ------------------ ------------------------- ---------------CONTROL FILE                  0             0        0REDO LOG                      0             0        0ARCHIVED LOG                  0             0        0BACKUP PIECE                  0             0        0IMAGE COPY                      0             0        0FLASHBACK LOG               1.95             0        2FOREIGN ARCHIVED LOG          0             0        07 rows selected.

Calculate space already occupied by the Flash recovery area

SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;SUM(PERCENT_SPACE_USED)*3/100-----------------------------            .0585SQL>  select archiver from v$instance;  ARCHIVE-------STARTEDSQL> select name from v$archived_log;NAME--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_947091054.dbf

Force Switch Archive

SQL> alter system switch logfile;System altered.SQL> select name from v$archived_log;NAME--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_947091054.dbf/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch_1_8_947091054.log

Oracle production Environment Flash back, archive operation reference