Manual hot Standby (open library status)
Backup control Files:
ALTER DATABASE backup Controlfile to '/U01/ORADATA/PROD/CON.BAK1 ';
Back up the data file (use PL/SQL for batch processing, paste the query results to run)
Begin
For I in (select Tablespace_name from dba_tablespaces) loop
Dbms_output.put_line (' Alter tablespace ' | | i.tablespace_name| | ' begin backup; ');
For j in (select file_name from dba_data_files where tablespace_name=i.tablespace_name) loop
Dbms_output.put_line (' host CP ' | | j.file_name| | '/home/oracle/hot_backup ');
End Loop;
Dbms_output.put_line (' Alter tablespace ' | | i.tablespace_name| | ' end backup; ');
End Loop;
End
Simulate the scene, for example
1. Delete emp1 table Emp1 Purge (not fully recovered)
Simulation:
drop table Emp1 Purge;
SELECT * from V$log;
alter system switch logfile;
/
Solve:
Select name from V$archived_log; (you can see the deleted log entries are switched to this archive log)
/u01/flash_recovery_area/prod/archivelog/2017_08_13/o1_mf_1_6_drzyyodl_.arc
/u01/flash_recovery_area/prod/archivelog/2017_08_27/o1_mf_1_7_dt4yy16z_.arc
/u01/flash_recovery_area/prod/archivelog/2017_08_27/o1_mf_1_8_dt4zlzff_.arc
/u01/flash_recovery_area/prod/archivelog/2017_08_27/o1_mf_1_9_dt4zow6g_.arc
/u01/flash_recovery_area/prod/archivelog/2017_08_27/o1_mf_1_1_dt5fpkxt_.arc
/u01/flash_recovery_area/prod/archivelog/2017_11_12/o1_mf_1_2_f0jh6xf2_.arc
/u01/flash_recovery_area/prod/archivelog/2017_11_12/o1_mf_1_3_f0jk4n4p_.arc
/u01/flash_recovery_area/prod/archivelog/2017_11_13/o1_mf_1_1_f0lkgyt6_.arc
/u01/flash_recovery_area/prod/archivelog/2017_11_13/o1_mf_1_2_f0lo0fom_.arc
Because today 13th, so choose these two logs, you can switch more than a few times to generate several log entries
Next time mining (based on point-in-time recovery)
Mkdir-p/HOME/ORACLE/LOGMNR
Show Parameter Utl_file_dir
Alter system set utl_file_dir= '/HOME/ORACLE/LOGMNR ' scope=spfile;
Startup force;
Show parameter Utl_file_dir;
ALTER DATABASE add supplemental log data;
Execute dbms_logmnr_d.build (' Dict.ora ', '/HOME/ORACLE/LOGMNR ', dbms_logmnr_d.store_in_flat_file);
Execute Dbms_logmnr.add_logfile (logfilename=> '/u01/flash_recovery_area/prod/archivelog/2017_11_13/o1_mf_1_1_ F0lkgyt6_.arc ', options=>dbms_logmnr.new);
Execute Dbms_logmnr.add_logfile (logfilename=> '/u01/flash_recovery_area/prod/archivelog/2017_11_13/o1_mf_1_2_ F0lo0fom_.arc ', options=>dbms_logmnr.addfile);
Execute DBMS_LOGMNR.START_LOGMNR (dictfilename=> '/home/oracle/logmnr/dict.ora ', Options=>dbms_logmnr.ddl_ dict_tracking);
Select Username,scn,to_char (timestamp, ' yyyy-mm-dd hh24:mi:ss ') Time,sql_redo from v$logmnr_contents WHERE Lower (sql_ Redo) Like ' drop table% ';
Execute DBMS_LOGMNR.END_LOGMNR;
USERNAME SCN Time
------------------------------ ---------- -------------------
Sql_redo
------------------------------------------------------------
UNKNOWN 954522 2017-11-13 16:03:53
drop table Emp1 Purge;
(Recorded this time 2017-11-13 16:03:53)
Shutdown abort;
Time digging is complete.
Cd/home/oradata/prod
RM *.dbf
CP/HOME/ORACLE/HOT_BACKUP/*.DBF./
Startup
Recover database until time ' 2017-11-13 16:03:53 '; auto
ALTER DATABASE open resetlogs;
Verify View
SELECT * from EMP1;
Successfully retrieve this table, also can look for recovery through SCN number, but can not use flash back table because I was purge completely deleted, can only not fully recover.
2. Simulate USER01.DBF Media damage
Rm-rf/u01/oradata/prod/users01.dbf
For example shutdown immediate
[Email Protected]>shutdown Immediate
ORA-01116: Error opening database file 4 o'clock
ORA-01110: Data file 4: '/U01/ORADATA/PROD/USERS01.DBF '
ORA-27041: Unable to open file
Solve:
alter system flush Buffer_cache;
Alter TABLESPACE users offline immediate;
cp/home/oracle/hot_backup/users01.dbf/u01/oradata/prod/
Recover Tablespace Users;auto
Alter tablespace users online;
3. Simulate UNDOTBS1 media corruption (methods of recovery I only demo based on file number recovery)
Select name from V$datafile;
Rm-rf/u01/oradata/prod/undotbs01.dbf
Shutdown abort
Solve:
[Email protected]>select file#,error from V$recover_file;
file# ERROR
---------- -----------------------------------------------------------------
1 UNKNOWN ERROR
2 UNKNOWN ERROR
3 FILE not FOUND
[Email protected]>select file#,name from V$datafile;
file# NAME
--------------------------------------------------
1/u01/oradata/prod/system01.dbf
2/u01/oradata/prod/sysaux01.dbf
3/u01/oradata/prod/undotbs01.dbf
4/u01/oradata/prod/users01.dbf
5/u01/oradata/prod/example01.dbf
[Email protected]>select file#,checkpoint_change# from V$datafile_header;
file# checkpoint_change#
---------- ------------------
1 975649
2 975649
3 0
4 975649
5 975649
Determine if file 3rd is damaged
cp/home/oracle/hot_backup/undotbs01.dbf/u01/oradata/prod/
Recovere datafile 3;auto
ALTER DATABASE open;
4. Current log, media corruption
CREATE TABLE t2 (id int);
INSERT into T2 values (100);
Commit
Alter system archive log current;
INSERT into T2 values (200);
Commit
SELECT * from Scott.t2;
Select Group#,sequence#,status from V$log;
Select Group#,member from V$logfile;
Find out the current log is the group number is 1 corresponding to the Redo01.log
Analog Rm-rf/u01/oradata/prod/redo01.log
Shutdown abort
Startup
The database is loaded.
ORA-00313: Unable to open member of Log Group 1 (for thread 1) ORA-00312:
Online log 1 Thread 1: '/u01/oradata/prod/redo01.log '
ORA-27037: Unable to get file status Linux
Error:2: No such file or directory
Additional Information:3
Solve:
cd/u01/oradata/prod/
RM *.dbf
CP/HOME/ORACLE/HOT_BACKUP/*.DBF./
Startup
Recover database until Cancel;auto
Recover database until Cancel;cancel
ALTER DATABASE open resetlogs;
SELECT * from Scott.t1;
Id
----------
100
5. Control file corruption
Rm-rf/u01/oradata/prod/control01.ctl
Rm-rf/u01/flash_recovery_area/prod/control02.ctl
Shutdown abort
Startup
ORA-00205:?????????,??????,???????
Select status from V$instance;
Solve:
cp/home/oracle/control01.ctl/u01/oradata/prod/
cp/home/oracle/control02.ctl/u01/flash_recovery_area/prod/
Recover database using Backup Controlfile;auto
Recover database using Backup controlfile; the current log
ALTER DATABASE open resetlogs;
Backup and recovery in Oracle database hot standby and examples