Oracle Database hot backup and recovery and example, oracle Database

Oracle Database hot backup and recovery and example, oracle Database

Manual Hot Standby (Database opening status)

Backup control file:

Alter database backup controlfile to '/u01/oradata/prod/con. bak1 ';

Back up data files (here pl/SQL is used for batch processing, and the query results can be pasted and run)

Begin
For I in (select tablespace_name from dba_tablespaces) loop
Dbms_output.put_line ('alter tablespace '| I. tablespace_name | 'in in 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;

Simulation site, for example

1. Delete the emp1 table drop table emp1 purge; (Incomplete recovery)

Simulation:

Drop table emp1 purge;

Select * from v $ log;

Alter system switch logfile;

/

Solution:

Select name from v $ archived_log; (the deleted log entries are switched to this archive log)

/U01/flash_recovery_area/PROD/archivelog/pai_08_13/o1_mf_00006_drzyyodl _. arc
/U01/flash_recovery_area/PROD/archivelog/pai_08_27/o1_mf_20177_dt4yy16z _. arc
/U01/flash_recovery_area/PROD/archivelog/pai_08_27/o1_mf_00008_dt4zlzff _. arc
/U01/flash_recovery_area/PROD/archivelog/pai_08_27/o1_mf_00009_dt4zow6g _. arc
/U01/flash_recovery_area/PROD/archivelog/pai_08_27/o1_mf_1_1_dt5fpkxt _. arc
/U01/flash_recovery_area/PROD/archivelog/pai_11_12/o1_mf_1_2_f0jh6xf2 _. arc
/U01/flash_recovery_area/PROD/archivelog/pai_11_12/o1_mf_1_3_f0jk4n4p _. arc
/U01/flash_recovery_area/PROD/archivelog/1__11_13/o1_mf_1_f0lkgyt6 _. arc
/U01/flash_recovery_area/PROD/archivelog/pai_11_13/o1_mf_1_2_f0lo0fom _. arc

Because the two logs are selected on the 13th of the current day, you can also switch to the next day to generate several more log entries.

Next, we will perform time mining (recovery based on time points)

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/pai_11_13/pai_. arc', options => dbms_logmnr.new );

Execute dbms_logmnr.add_logfile (logfilename => '/u01/flash_recovery_area/PROD/archivelog/pai_11_13/pai_. 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 16:03:53
Drop table emp1 purge;

(Record this time 16:03:53)

Shutdown abort;

Time mining is completed.

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;

View Verification

Select * from emp1;

If the table is successfully retrieved, you can also use the scn number for restoration. However, you cannot use the flashback table because it is completely deleted by purge and cannot be completely recovered.

2. Simulate damage to the user01.dbf Media

Rm-rf/u01/oradata/prod/users01.dbf

For example, shutdown immediate

SYS @ prod> shutdown immediate
ORA-01116: An error occurred while opening database file 4
ORA-01110: data file 4: '/u01/oradata/prod/users01.dbf'
ORA-27041: Unable to open file

Solution:

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 medium damage (multiple restoration methods I only demonstrate restoration by file number)

Select name from v $ datafile;

Rm-rf/u01/oradata/prod/undotbs01.dbf

Shutdown abort

Solution:

SYS @ prod> select FILE #, ERROR from v $ recover_file;

FILE # ERROR
---------------------------------------------------------------------------
1 UNKNOWN ERROR
2 UNKNOWN ERROR
3 FILE NOT FOUND

SYS @ prod> 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

SYS @ prod> select file #, checkpoint_change # from v $ datafile_header;

FILE # CHECKPOINT_CHANGE #
----------------------------
1 975649
2 975649
3 0
4 975649
5 975649

Make sure file 3 is damaged.

Cp/home/oracle/hot_backup/undotbs01.dbf/u01/oradata/prod/

Recovere datafile 3; auto

Alter database open;

4. The current log shows corrupted media.

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;

Check whether the current log is redo01.log corresponding to group number 1.

Simulate rm-rf/u01/oradata/prod/redo01.log

Shutdown abort

Startup

The database has been loaded.
ORA-00313: Unable to open the member ORA-00312 for log group 1 (for thread 1:
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

Solution:

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;

Solution:

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; Current Log

Alter database open resetlogs;