[Oracle Database] Hand slip delete wrong data, step by step teach you how to save?

Often walk by the river, how can not wet shoes?

Today, a customer contacted me and said that the data table was updated by mistake, which caused the data disorder. I hope you can help to recover it. The database version is 11.2.0.1. The operating system is Windows64 bit. The data has been changed for more than one week.

1. The data has been misoperated for more than a week, so the use of UNDO snapshot is excluded. 2. If there is no DG Dr Environment, the DG flashback is excluded. 3. The archive mode has been enabled for the primary database, and RMAN backup exists. You can use RMAN to restore the corresponding tablespace of the table, and use DBLINK to retrieve the data table. 4. Oracle 12C supports single table recovery.

1. The primary database queries the tablespaces corresponding to the incorrectly updated tablespaces and tablespaces that do not need to be restored. 2. Oracle 11.2.0.1 database software is installed on the new host. You do not need to create a database. 3. Copy the parameter file and password file to the new host, and modify the parameter file and the directory required for creating a new instance according to the new host. 4. The new host uses the modified parameter file to open the database instance to the nomount state. 5. The primary library copies the backup control file to the new host, the new host uses RMAN to restore the control file, and MOUNT the new instance. 6. The new host RESTORE TABLESPACE is restored to 2021/06/08 16:00:00. 7. The new host RECOVER DATABASE SKIP TABLESPACE is restored to 2021/06/08 16:00:00. 8. Set the new host instance to read-only mode. 9. Verify that the table data of the new host instance is correct. If not, repeat step 7 to adjust the time point to 2021/06/08 17:00:00 to restore. 10. The primary library creates a DBLINK that connects to the new host instance and retrieves table data from the new host instance.

2. Simulate the test scenario

sqlplus / as sysdba
Create table space
create tablespace lucifer datafile '/oradata/orcl/lucifer01.dbf' size 10M autoextend off;
create tablespace ltest datafile '/oradata/orcl/ltest01.dbf' size 10M autoextend off;
Create user
create user lucifer identified by lucifer;
grant dba to lucifer;
- create a table
conn lucifer/lucifer
create table lucifer(id number not null,name varchar2(20)) tablespace lucifer;
-- Insert data
insert into lucifer values(1.'lucifer');
insert into lucifer values(2.'test1');
insert into lucifer values(3.'test2');
commit;
Copy the code

sqlplus / as sysdba
conn lucifer/lucifer
delete from lucifer where id=1;
update lucifer set name='lucifer' where id=2;
commit;
Copy the code

Note: To simulate the customer environment, assume that the UNDO snapshot cannot be retrieved and the current deletion point in time is:2021/06/17 18:10:00.

The primary database queries the tablespaces corresponding to the incorrectly updated tables and tablespaces that do not need to be restored

Select * from tablespace where tablespace is incorrectly updated
select owner,tablespace_name from dba_segments where segment_name='LUCIFER';
Query all tablespaces
select tablespace_name from dba_tablespaces;
Copy the code

The primary library copies the parameter files and password files to the new host, and modifies the parameter files and directories required for creating new instances based on the new host

## generate pfile parameter file
sqlplus / as sysdba
create pfile='/home/oracle/pfile.ora' from spfile;
## Copy to new hostSu - oracle SCP /home/oracle/pfile.ora 10.211.55.112:/ TMP SCP$ORACLE_HOME/ DBS/orapworcl 10.211.55.112:$ORACLE_HOME/dbs
### New host according to the actual situation to modify the parameter file and create a directory
mkdir -p /u01/app/oracle/admin/orcl/adump
mkdir -p /oradata/orcl/
mkdir -p /archivelog
chown -R oracle:oinstall /archivelog
chown -R oracle:oinstall /oradata
Copy the code

The new host uses the modified parameter file to open the database instance to the nomount state

sqlplus / as sysdba
startup nomount pfile='/tmp/pfile.ora';
Copy the code

The primary library copies the backup control file to the new host. The new host uses RMAN to restore the control file and mounts the new instance

rman target /
list backup of controlfile;
## Copy backup files to the new hostSCP/backup/backlv0_ORCL_20210617_107548592 * 10.211.55.112: / TMP SCP/u01 / app/oracle/product / 11.2.0 / db/DBS / 0 c01l775_1_1 10.211.55.112: / TMP## The new host restores the control file and opens it to the mount state
rman target /
restore controlfile from '/tmp/backlv0_ORCL_20210617_1075485924_9_1';
alter database mount;
Copy the code

## Query the archive log number time required for recovery
alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"; 
select first_time,sequence# from v$archived_log where sequence#='7';

## Vomit out the required archive logs by backing up RESTORE
rman target / 
catalog start with '/tmp/0c01l775_1_1'; 
crosscheck archivelog all; 
run { 
allocate channel ch01 device type disk; 
SET ARCHIVELOG DESTINATION TO '/archivelog';
restore ARCHIVELOG SEQUENCE 7; 
release channel ch01; 
}

## recover to the specified time point 2021-06-17 18:06:00
run { 
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; 
set until time 'the 2021-06-17 18:06:00'; 
allocate channel ch01 device type disk; 
recover database skip tablespace LTEST,EXAMPLE; 
release channel ch01; 
} 
Copy the code

The new host instance is enabled in read-only mode

sqlplus / as sysdba
alter database open read only;
Copy the code

Verify that the table data for the new host instance is correct

select * from lucifer.lucifer;
Copy the code

Anyway, you can say anything with a backup. Therefore, as a DBA, backup must be done well!!