Oracle Database recovery and backup

Oracle Database recovery and backup 1. oracle database recovery 1. restore the deleted data. delete from emp e where e. empname = 'Smith 'select * from flashback_transaction_query f where f. the statements below table_name = 'emp' UNDO _ SQL are the opposite operations for deleting data, run this statement to restore the deleted data. In version 11g, UNDO_ SQL is null. alter database add supplemental log data 2. restore the newly updated data to a certain point in time to update emp e set e. job = 'cler' select sysdate from dual; check whether the data at the specified time point is the data before recovery: select * from emp as of timestamp to_timestamp ('2014/1/30 21:10:19 ', 'yyyy-mm-dd hh24: mi: ss') alter table emp enable row movement; restore to a certain time point: flashback table emp to timestamp to_timestamp ('2017/30 21:10:19 ', 'yyyy-mm-dd hh24: mi: ss') 3. restore the deleted table drop table bonus flashback table bonus to before drop to view the deleted table: select * from user_recyclebin u order by u. droptime desc II. oracle Database Backup 1. database RMAN backup needs to view archive mode $ sqlplus/as sysdbaSQL> Archive log listDatabase log mode No archive ModeAutomatic archival DisabledArchive destination offline online log sequence 18 Current log sequence 20 change Archive mode mode: Disable Database SQL> shutdown immediate start database to mount status SQL> startup mount change to archive mode (noarchivelog is not archive Mode) SQL> alter database archivelog; check whether the schema SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 18 Next log sequence to archive 20 Current log sequence 20 to open RMAN $ rman target/View RMAN> show all; using target database control file instead of recovery catalogRMAN configuration parameters for database with db_unique_name ORCL are: configure retention policy to redundancy 1; # defaultCONFIGURE backup optimization off; # defaultCONFIGURE default device type to disk; # defaultCONFIGURE controlfile autobackup off; # defaultCONFIGURE controlfile autobackup format for device type disk to '% F'; # defaultCONFIGURE device type disk parallelism 1 backup type to backupset; # defaultCONFIGURE datafile backup copies for device type disk to 1; # defaultCONFIGURE archivelog backup copies for device type disk to 1; # defaconfigconfigure maxsetsize to unlimited; # defaultCONFIGURE encryption for database off; # defaconfigconfigure encryption algorithm 'aes128 '; # defaconfigconfigure compression algorithm 'basic' as of release 'default' optimize for load true; # defaconfigconfigure archivelog deletion policy to none; # defaultCONFIGURE snapshot controlfile name to '/app/oracle/product/11.1.0/db_1/dbs/snapcf_ixdba.f'; # default: configure controlfile autobackup off; that is, control files are not backed up by default, you need to change it to the default backup control file RMAN> configure controlfile autobackup on; database full-database backup RMAN> backup database; view backup information RMAN> list backup; list of Backup Sets ============================ BS Key Type LV Size Device Type Elapsed Time Completion Time ---------------------------------------------- --------------- 1 Full 1006.31 m disk 00:08:09 31-AUG-13 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20130831T025434 Piece Name:/app/oracle/flash_recovery_area/ORCL/backupset/2013_08_31/o1_mf_nnndf_TAG20130831T025434_923hbw6f _. bkp List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Name ---- ------------- ---- 1 Full 1037059 31-AUG-13/app/oracle/oradata/orcl/system01.dbf 2 Full 1037059 31-AUG-13 /app/oracle/oradata/orcl/sysaux01.dbf 3 Full 1037059 31-AUG-13/app/oracle/oradata/orcl/oracle 4 Full 1037059 31-AUG-13/app/oracle/oradata/orcl/users01.dbf 5 Full 1037059 31-AUG-13/app/oracle/oradata/orcl/bank_data01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ------------ ----------- ----------------- 2 Full 9.33 m disk 00:00:04 31-AUG-13 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: tag20130831t0301_piece Name:/app/oracle/flash_recovery_area/ORCL/autobackup/2013_08_31/o1_mf_n_1_870625_923htgw8 _. bkp Control File Included: Ckp SCN: 1037059 Ckp time: 31-AUG-13 III. During database recovery from the oracle database, locate the initialization spfile or pfile in nomount state; find the control File Contral File Based on the initialization File, which is in the mount state; find the Data File and Redo log File based on the control File, which is in the open state; the premise for restoring the database is that the Oracle database can initialize the spfile file, control file, redo log, archive log, and backup. The Restoration principle is as follows: Based on the lost files, start the database to the corresponding state, restore the corresponding files through RMAN, and then start the database to the next state to check whether there is a backup RMAN> list backup summary; 1. delete data files only when data files are lost # rm-rf *. dbf starts Database SQL> startupORACLE instance started. total System Global Area 527290368 bytesFixed Size 1337660 bytesVariable Size 318768836 bytesDatabase Buffers 201326592 bytesRedo Buffers 5857280 bytesDatabase mounted. ORA-01157: cannot identify/lock data file 1-see DBWR trace fileORA-01110: data file 1: '/app/oracle/oradata/orcl/system01.dbf' enter RMAN $ rman target/restore data file RMAN> restore database; Starting restore at 31-AUG-13using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID = 20 device type = DISK channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile (s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to/app/oracle/oradata/orcl/system01.dbfchannel ORA_DISK_1: restoring datafile 00002 to/app/oracle/oradata/orcl/sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00003 to/app/oracle/oradata/orcl/undotbs01.dbfchannel ORA_DISK_1: restoring datafile 00004 to/app/oracle/oradata/orcl/users01.dbfchannel ORA_DISK_1: restoring datafile 00005 to/app/oracle/oradata/orcl/bank_data01.dbfchannel ORA_DISK_1: reading from backup piece/app/oracle/flash_recovery_area/ORCL/backupset/2013_08_31/empty _. bkpchannel ORA_DISK_1: piece handle =/app/oracle/flash_recovery_area/ORCL/backupset/2013_08_31/o1_mf_nnndf_TAG20130831T025434_923hbw6f _. bkp tag = TAG20130831T025434channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00: 05: 58 Finished restore at 31-AUG-13 RMAN> recover database; starting recover at 31-AUG-13using channel ORA_DISK_1 starting media recoverymedia recovery complete, elapsed time: 00:00:07 Finished recover at 31-AUG-13 when the SQL statement is changed to open, the SQL statement is restored successfully.> alter database open; 2. lost redo log file # rm-rf *. logSQL> recover database until cancel; SQL> alter database open resetlogs; 3. loss Control File, redo log file, data file RMAN> restore controlfile from autobackup; RMAN> alter database mount; RMAN> restore database; SQL> recover database using backup controfile until cancel; SQL> alter database open resetlogs; 4. when the initialization file is also lost, SQL> startup fpile = '/app/oracle/admin/orcl/pfile/init. ora.2220151118 '; RMAN> restore spfile from autobackup; SQL> startup nomount; other steps are the same as the loss of control files