Oracle production Environment Flash back, archive operation reference

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.