Manual manual Cold Standby Incomplete Recovery introduction (purge table incomplete recovery)

Not fully recovered


Basic types of incomplete restores:
1) based on point-in-time (until): Restores the entire database to a previous point in time
2) based on SCN (until change): Restores the entire database to a previous SCN
3) based on cancel (until cancel): Restores the entire database to the archive log or to the breakpoint of the current log


Incomplete recovery (incomplete recover) applicable environment:
1) Important data has been compromised at some point in the past.
2) When doing a full recovery, the archive log or the current online redo log is lost
3) When table space is deleted by mistake (with control file backup)
4) All control files are lost, when the backup control file is restored (fully recoverable when conditions are met)


Traditional procedure for incomplete recovery:
1) First to find the wrong operation point in time by LOGMNR
2) New fully prepared for the current database
3) Restore all datafile before that point in time
4) in the Mount state, to the database to do recover, restore to the wrong time point of operation
5) Make a logical backup of the recovered table (exp)
6) Restore the full backup again
7) Import the exported table to database (IMP)


Lab 1: Restore a table (incomplete recovery based on point-in-time) at a point in time in the past: cold backup, log, archive complete


1) Prepare experimental data


Sql> select * from Andy;


Id
----------
4


Sql> insert INTO Andy values (5);


1 row created.


Sql> commit;


Commit complete.


Sql> drop table Andy Purge;


Table dropped.


2) View log, archive environment


Sql> Set Linesize 400
Sql> select * from V$log;


group# thread# sequence# BYTES BLOCKSIZE members ARC STATUS first_change#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
1 1 52428800 1 NO current 1991534
2 1 52428800 1 YES INACTIVE 1959769
3 1 52428800 1 YES INACTIVE 1966481


sql> alter system switch logfile;


System altered.


Sql> select name from V$archived_log;
NAME
----------------------------------------------------------------------------------------------------------




/home/oracle/archivelog/orcl/archivelog/2014_12_01/o1_mf_1_47_b7rwlclg_.arc
/home/oracle/app/flash_recovery_area/orcl/archivelog/2014_12_01/o1_mf_1_48_b7ryjgng_.arc
/home/oracle/app/flash_recovery_area/orcl/archivelog/2014_12_01/o1_mf_1_49_b7rykz3l_.arc
/home/oracle/app/flash_recovery_area/orcl/archivelog/2014_12_01/o1_mf_1_50_b7ryn3fl_.arc
/home/oracle/archivelog/1_51_860522448.dbf
/home/oracle/archivelog/1_52_860522448.dbf
/home/oracle/archivelog/1_53_860522448.dbf
/home/oracle/archivelog/1_54_860522448.dbf
/home/oracle/archivelog/1_55_860522448.dbf


NAME
----------------------------------------------------------------------------------------------------------
/home/oracle/archivelog/1_56_860522448.dbf
/home/oracle/archivelog/1_57_860522448.dbf
/home/oracle/archivelog/1_58_860522448.dbf
/home/oracle/archivelog/1_59_860522448.dbf
/home/oracle/archivelog/1_60_860522448.dbf
/home/oracle/archivelog/1_61_860522448.dbf


Rows selected.


3) LOGMNR Log Mining to find the purge point in time.


--log mining at least to open suppleme, if not open, the information will be error
Sql> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from V$database;


Suppleme sup sup
-------- --- ---
YES No No
Sql> Col member for A50;
Sql> select * from V$logfile;


group# STATUS TYPE MEMBER is_
---------- ------- ------- -------------------------------------------------- ---
3 Online/home/oracle/app/oradata/orcl/redo03.log NO
2 Online/home/oracle/app/oradata/orcl/redo02.log NO
1 Online/home/oracle/app/oradata/orcl/redo01.log NO


Sql> select Group#,status from V$log;


group# STATUS
---------- ----------------
1 ACTIVE
2 Current
3 INACTIVE


Sql> select name from V$archived_log where name is not NULL for order by 1;


NAME
-----------------------------------------------------------------------------------------
/home/oracle/app/flash_recovery_area/orcl/archivelog/2014_12_01/o1_mf_1_48_b7ryjgng_.arc
/home/oracle/app/flash_recovery_area/orcl/archivelog/2014_12_01/o1_mf_1_49_b7rykz3l_.arc
/home/oracle/app/flash_recovery_area/orcl/archivelog/2014_12_01/o1_mf_1_50_b7ryn3fl_.arc
/home/oracle/archivelog/1_51_860522448.dbf
/home/oracle/archivelog/1_52_860522448.dbf
/home/oracle/archivelog/1_53_860522448.dbf
/home/oracle/archivelog/1_54_860522448.dbf
/home/oracle/archivelog/1_55_860522448.dbf
/home/oracle/archivelog/1_56_860522448.dbf
/home/oracle/archivelog/1_57_860522448.dbf
/home/oracle/archivelog/1_58_860522448.dbf


NAME
-----------------------------------------------------------------------------------------
/home/oracle/archivelog/1_59_860522448.dbf
/home/oracle/archivelog/1_60_860522448.dbf
/home/oracle/archivelog/1_61_860522448.dbf
/home/oracle/archivelog/orcl/archivelog/2014_12_01/o1_mf_1_47_b7rwlclg_.arc


Rows selected.


--Use Redolog log for mining


Sql> EXECUTE DBMS_LOGMNR. Add_logfile (logfilename=> '/home/oracle/app/oradata/orcl/redo01.log ', options=>dbms_logmnr.new);


PL/SQL procedure successfully completed.


Sql>execute DBMS_LOGMNR. Add_logfil (logfilename=> '/home/oracle/app/oradata/orcl/redo02.log ', OPTIONS=>DBMS_LOGMNR. AddFile);


PL/SQL procedure successfully completed.


Sql>execute DBMS_LOGMNR. Add_logfile (logfilename=> '/home/oracle/app/oradata/orcl/redo03.log ', OPTIONS=>DBMS_LOGMNR. AddFile);


PL/SQL procedure successfully completed.


Sql> Execute DBMS_LOGMNR. START_LOGMNR (Options=>dbms_logmnr.dict_from_online_catalog);


PL/SQL procedure successfully completed.


Sql> col username for A10;
Sql> Col Sql_redo for A45;
Sql> Select Username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name= ' ANDY ' ORDER by SCN;


USERNAME SCN TIMESTAMP Sql_redo
---------- ---------- ------------------- ---------------------------------------------
Andy 2000934 2014-12-11 09:47:15 drop table Andy Purge; Find Purge time


--Log mining with archive


Sql> Show Parameter Utl


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Create_stored_outlines string
Utl_file_dir string


Sql> alter system set utl_file_dir= '/HOME/ORACLE/LOGMNR ' scope=spfile;


System altered.


sql> startup force;


Sql> Show parameter utl;


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Create_stored_outlines string
Utl_file_dir STRING/HOME/ORACLE/LOGMNR


Sql> Execute dbms_logmnr_d.build (' Dict.ora ', '/HOME/ORACLE/LOGMNR ', dbms_logmnr_d.store_in_flat_file);


PL/SQL procedure successfully completed.


Sql> Execute dbms_logmnr.add_logfile (logfilename=> '/home/oracle/archivelog/1_61_860522448.dbf ', options= >dbms_logmnr.new);


PL/SQL procedure successfully completed.


Sql> Execute DBMS_LOGMNR.START_LOGMNR (dictfilename=> '/home/oracle/logmnr/dict.ora ', Options=>dbms_ logmnr.ddl_dict_tracking);


PL/SQL procedure successfully completed.


Sql> Select Username,scn,to_char (timestamp, ' yyyy-mm-dd hh24:mi:ss '), Sql_redo from V$logmnr_contents WHERE Lower ( Sql_redo) Like ' drop table% ';


USERNAME SCN to_char (TIMESTAMP, ' Sql_redo
------------------------------ ---------- ------------------- --------------------------------------------------
Andy 2000934 2014-12-11 09:47:15 drop table Andy Purge;


Sql> Execute DBMS_LOGMNR.END_LOGMNR;


PL/SQL procedure successfully completed.




4) Close the database, remove all DBF, prepare to do incomplete recovery


sql> shutdown immdiate;
[Email protected] logmnr]$ cd/home/oracle/app/oradata/orcl/
[Email protected] orcl]$ RM-RF *.dbf


5) Restore all backed-up data files


[Email protected] orcl]$ cp/home/oracle/coldbak/*.dbf/home/oracle/app/oradata/orcl/


6) do a point-in-time incomplete recovery based on the information provided by log Miner


sql> recover database until time ' 2014-12-11 09:47:15 ';
Ora-00279:change 1968596 generated at 12/10/2014 06:26:35 needed for thread 1
Ora-00289:suggestion:/home/oracle/archivelog/1_60_860522448.dbf
Ora-00280:change 1968596 for thread 1 are in sequence #60




Specify log: {<ret>=suggested | AUTO | CANCEL}
Auto
Ora-00279:change 1991534 generated at 12/10/2014 07:25:45 needed for thread 1
Ora-00289:suggestion:/home/oracle/archivelog/1_61_860522448.dbf
Ora-00280:change 1991534 for thread 1 are in sequence #61




Log applied.
Media recovery complete.


Note: If the archive log is used during the recovery process, enter auto. To use the current log, enter the filename.


7) Resetlogs Way to open the database


sql> ALTER DATABASE open resetlogs;


Database altered.


8) Verification
Sql> select * from Andy;


Id
----------
5
4


OK, reprint please indicate the source.

Manual manual Cold Standby Incomplete Recovery introduction (purge table incomplete recovery)