Rogerio Antonio

EnglishVersion Neste post partilho como como usar tecnologia flashback - neste caso flashback database Como exemplo vou mostar como ajuda num dos piores cenarios - num truncate O Truncate e muito usado para limpar as tabelas, nao so os dados mas os segmentos. para recuperar de um trunncate e necessario restaurar a base de dados. Importante flashback database e necessario para operacoes de failover do data guard. Activar flashback database Requisitos para flashback database - A base de dados em archivelog mode - tradicional archive redo logs - Feature flashback activada - stream the logs / flashback logs Para isso e necessario reiniciar a base de dados de uma forma consistente Vou criar tambem um RESTORE POINT com opcao GUARANTEE FLASHBACK DATABASE -- As '/ as sysdba', SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 849530880 byt...

EnglishVersion This post is about using flashback technology for recover a database, using as example, how to recover a table after truncate. Truncate is used to clean the tables, it also cleans the table segments. To recover trucated data requires to .. recover the database. Important note : flashback database is a must in case of failover in data guard operations. Flashback requirements It have some requirements for flashback database - The DB needs to be in archive log mode -the traditional archive redo logs - Feature flashback ON - it will activate the stream the logs / flashback logs For all this work the database needs to be mounted in a consitant maner To help with demo I will also create a RESTORE POINT with GUARANTEE FLASHBACK DATABASE -- As '/ as sysdba', SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global...

PT :   Brincar com ARCHIVED LOGs EN :  Play with ARCHIVED LOGs

PT :   +FRA - Gestão de espaço EN :  +FRA - Maintenance 

PT  : E a tal query que nao termina .. EN : And about that query that never ends ...

 FRA – Flash Recover area   Central location for backup data Logs Archive logs Backups Control file Location a ASM filesystem Allow RMAN Retention policies Parameters DB_RECOVERY_FILE_DEST DB_RECOVERY_DEST_SIZE  How to ..  --- check the flash_recovery_area parameters SQL> show parameter DB_RECOVERY_FILE_DEST NAME                                             TYPE               VALUE ------------------------------------         -----------         ------------------------------ db_recovery_file_dest        ...

RMAN> list backup; List of Backup Sets =================== BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1       Full    281.02M    DISK        00:02:23     21-DEC-12              BP Key: 1   Status: AVAILABLE  Compressed: YES  Tag: TAG20121221T014321         Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_12_21/o1_mf_nnndf_TAG20121221T014321_8f7d5blz_.bkp   List of Datafiles in backup set 1   File LV Type Ckp SCN    Ckp Time  Name   ---- -- ---- ---------- --------- ----   1       Full 923674   ...

@  backup as compressed backupset database RMAN> backup as compressed backupset database; Starting backup at 21-DEC-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=34 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1: starting piece 1 at 21-DEC-12 channel ORA_DISK_1: finished piece 1 at 21-DEC-12 piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_12_21...

Retention Policy Setting to specify how long to keep backups Any backups that are not needed to support this recovery window are marked as OBSOLETE and are automatically removed by RMAN if you are using a flash recovery area, if you are no user FRA  the deletion need to be done Manually  Applies to full back up only (level 0) not to level 1 Backups can be automatically retained and managed using one of two methods:  Redundancy :  X number of backups Default !! Recovery Window :  X number of days Recommended by Oracle How to .. RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP O...

Control file RMAN always stores its metadata in the target database  control file Whether you use or not the Recovery Catalog Oracle stores metadata into the targer database control file RMAN info on control file is is agedbased on CONTROL_FILE_RECORD_KEEP_TIME Single point control ( control file) Does not required another DB How to .. [oracle@oracle ~]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Sun Feb 3 17:46:29 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. connected to target database: ORCL (DBID=1272537938) Recovery Catalog keep info for long time single point to store many databases RESYNC CATALOG, syncronize the control file metadata with the recover catalog Can store "RMAN backup scripts" in the catalog ... is not possible do it on the control file Recovery Catalog control views RC_* Allow history commands BACKUP .. KEEP...

Set Archive log SQL> select startup_time from  v$instance; STARTUP_Time --------- 19-DEC-12 SQL> select dbid,name,log_mode,flashback_on from  v$database;       DBID        NAME       LOG_MODE          FLASHBACK_ON ----------          ---------      ------------               ------------------ 1272537938   ORCL          NOARCHIVELOG    NO SQL>  archive log list; Database log mode              No Archive Mode Automatic archival             Disabled Archive destination  ...