oracle flash_recovery_area

출처: http://radiocom.kunsan.ac.kr/lecture/oracle/backup_restore/flash_recovery_area.html

flash_recovery_area 

---

 

오라클의 RMAN(recovery manager)을 사용하여 생성되어지는 백업 파일들은 db_recovery_file_dest 파라미터에서 지정한 위치에 저장되어진다.configuring the flash recovery area 참조

현재 SID=ORCL인 데이터베이스의 경우에는 $ORACLE_HOME/dbs/spfileorcl.ora에서 지정한 곳을 저장공간으로 사용하고 있으며, v$parameter 뷰를 통해서 flashback_recovery_area를 확인할 수 있다.

db_flashback_retention_target에서 백업 파일의 저장 기간을 설정.변경할 수 있다.

Flash Recovery Area를 Enabling하기 위해서는 다음 표의 초기 파라미터 값을 설정해 주어야 한다. 

initialization parameter	요구조건
DB_RECOVERY_FILE_DEST_SIZE	yes
DB_RECOVERY_FILE_DEST	yes
DB_FLASHBACK_RETENTION_TARGET	No

 

 

【예제】
SQL> select value,name from v$parameter
  2  where name='db_recovery_file_dest'
  3    or  name='db_recovery_file_dest_size';
 
VALUE                                                  NAME
------------------------------------------------------ --------------------------
/export/home0/oracle/app/oracle/flash_recovery_area    db_recovery_file_dest
2147483648                                             db_recovery_file_dest_size

SQL>
【예제】
$ cd /export/home0/oracle/app/oracle/product/10.1.3/dbs
$ ls
core            init.ora        lkORCL          snapcf_orcl.f
hc_orcl.dat     initdw.ora      orapworcl       spfileorcl.ora
$ cat spfileorcl.ora
] orcl.__db_cache_size=234881024
orcl.__java_pool_size=8388608
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=62914560
*.background_dump_dest='/export/home0/oracle/app/oracle/admin/orcl/bdump'
*.compatible='10.1.0.2.0'
*.control_files='/export/home0/oracle/app/oracle/oradata/orcl/control01.ctl','/export/home0/oracle/app/oracle/oradata/orcl/control02.ctl','/export/home0/oracle/app/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/export/home0/oracle/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/export/home0/oracle/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=69206016
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=314572800
*.sga_target=314572800
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/export/home0/oracle/app/oracle/admin/orcl/udump'
$  
 
SQL> 

Flash Recovery Area 최적크기 결정하기

 

【예제】
SQL> select value,name from v$parameter
  2    where name='db_recovery_file_dest'
  3      or  name='db_recovery_file_dest_size';

VALUE                                    NAME
---------------------------------------- ------------------------------
/export/home/oracle/flash_recovery_area  db_recovery_file_dest
4070572032                               db_recovery_file_dest_size

SQL>

단계1: V$archived_log 뷰를 확인한다.

SQL> select recid,name from v$archived_log;

     RECID NAME
---------- ---------------------------------------------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10 /export/home/oracle/oradata/orcl/redo01.log
        11 /export/home/oracle/oradata/orcl/redo02.log
        12 /export/home/oracle/oradata/orcl/redo03.log

12 rows selected.

SQL> 

단계 2: recovery area 사이즈를 설정한다.

SQL> alter system set 
  2   db_recovery_file_dest_size = 10G
  3   scope=BOTH SID='*';

System altered.

SQL>

단계 3: recovery area의 위치를 설정한다.
SQL> alter system set 
  2   db_recovery_file_dest = '+disk1'
  3   scope=BOTH SID='*';

System altered.

SQL>

단계 4: v$flashback_database_log 뷰를 확인한다.
SQL> select estimated_flashback_size
  2  from v$flashback_database_log;

no rows selected

SQL>