Backup and recovery in Oracle database hot standby and examples

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.