Backup and recovery in Oracle database hot standby and examples

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