Today, a customer contacted and said that the data table was updated by mistake, causing the data to be messed up, and I hope to help restore it. Database version 11.2.0.1 , the operating system is Windows64 bit data has been changed over one weeks time, the database is turned on archive mode , there is no DG disaster , there RMAN backup , restore hope to this table to the previous week specified point in time.

1.Analysis

Only the conventional recovery methods are listed below:

1.The data has been mishandled for more than a week, so it is ruled out to use UNDO snapshots to retrieve it.

2.There is no DG disaster recovery environment, and the use of DG flashback is excluded.

3.The archive mode has been turned on in the main database and RMAN backup exists.You can use RMAN to restore the table space corresponding to the table on a different machine, and use DBLINK to retrieve the data table.

4. Support single table recovery after Oracle 12C.

Conclusion: For safety's sake, use RMAN to restore the table space on a different machine to retrieve the data table.

2.Ideas

Customers want to restore the table data to 2021/06/08 17:00:00a point in time before.

The general steps are as follows:

1.The main database query incorrectly updates the table space corresponding to the data table and the table space that does not need to be restored.

2.Install the Oracle 11.2.0.1 database software on the new host, no need to build a database, and the directory structure is best to be consistent.

3.The main library copies the parameter file and password file to the new host, modify the parameter file according to the new host, and create the directory required for the new instance.

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

5.The main 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 the point in time 2021/06/08 16:00:00.

7.The new host RECOVER DATABASE SKIP TABLESPACE is restored to the point in time 2021/06/08 16:00:00.

8.The new host instance is turned on to read-only mode.

9, to confirm the new host instance of table data is correct, if not correct, repeat step 7 to adjust slowly to the point in time 2021/06/08  17:00:00to promote recovery.

10.The main database creates a DBLINK connected to the new host instance, and retrieves table data from the new host instance through DBLINK.

Note: The choice of tablespace recovery is because the data volume of the main database is relatively large, and it takes a lot of time to recover the entire database.

Three, test environment simulation

In order to desensitize the data, the test environment is used to simulate scenes for demonstration.

1.Environmental preparation

Environment deployment can be initialized through the Oracle one-click installation script and then manually installed.

Main library:./OracleShellInstall.sh -i 10.211.55.111 -m Y -txh Y

New host:./OracleShellInstall.sh -i 10.211.55.112 -m Y -txh Y

2.Simulation test scenario

The main library opens the archive mode

--设置归档路径alter system set log_archive_dest_1='LOCATION=/archivelog';--重启开启归档模式shutdown immediate
startup mountalter database archivelog；--打开数据库alter database open;

Create test data

sqlplus / as sysdba--创建表空间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 lucifer identified by lucifer;grant dba to lucifer;--创建表conn lucifer/lucifercreate table lucifer(id number not null,name varchar2(20)) tablespace lucifer;--插入数据insert into lucifer values(1,'lucifer');insert into lucifer values(2,'test1');insert into lucifer values(3,'test2');commit;

Full database preparation

run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
crosscheck backup;
crosscheck archivelog all; 
sql"alter system switch logfile";
delete noprompt expired backup;
delete noprompt obsolete device type disk;
backup database include current controlfile format '/backup/backlv0_%d_%T_%t_%s_%p';
backup archivelog all DELETE INPUT;
release channel c1;
release channel c2;
}

Simulation data modification

sqlplus / as sysdba
conn lucifer/luciferdelete from lucifer where id=1;update lucifer set name='lucifer' where id=2;commit;

Note: To simulate customer environments, it is assumed can not be retrieved by UNDO snapshots, delete the current time point: 2021/06/17 18:10:00.

If you use UNDO snapshots, it is more convenient:

--查找UNDO快照数据是否正确select * from lucifer.lucifer as of timestamp to_timestamp('2021-06-17 18:05:00','YYYY-MM-DD HH24:MI:SS');--将UNDO快照数据捞至新建表中create table lucifer.lucifer_0617 as select * from lucifer.lucifer as of timestamp to_timestamp('2021-06-17 18:05:00','YYYY-MM-DD HH24:MI:SS');

Four, RMAN complete recovery process

The main database query incorrectly updates the table space corresponding to the data table and the table space that does not need to be restored

--查询误更新数据表对应表空间select owner,tablespace_name from dba_segments where segment_name='LUCIFER';--查询所有表空间select tablespace_name from dba_tablespaces;

The main library copies the parameter file and password file to the new host, modify the parameter file according to the new host, and create the directory required for the new instance

##生成pfile参数文件sqlplus / as sysdba
create pfile='/home/oracle/pfile.ora' from spfile;##拷贝至新主机su - oracle
scp /home/oracle/pfile.ora 10.211.55.112:/tmp
scp $ORACLE_HOME/dbs/orapworcl 10.211.55.112:$ORACLE_HOME/dbs###新主机根据实际情况修改参数文件并且创建目录mkdir -p /u01/opt/oracle/admin/orcl/adump
mkdir -p /oradata/orcl/
mkdir -p /archivelog
chown -R oracle:oinstall /archivelog
chown -R oracle:oinstall /oradata

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';

The main library copies the backup control file to the new host, and the new host uses RMAN to restore the control file and MOUNT the new instance

rman target /
list backup of controlfile;##拷贝备份文件至新主机scp /backup/backlv0_ORCL_20210617_107548592* 10.211.55.112:/tmp
scp /u01/opt/oracle/product/11.2.0/db/dbs/0c01l775_1_1 10.211.55.112:/tmp##新主机恢复控制文件并开启到mount状态rman target /
restore controlfile from '/tmp/backlv0_ORCL_20210617_1075485924_9_1';
alter database mount;

By list backup of controlfile;you can see the control file location.



The new host RESTORE TABLESPACE is restored to the point in time2021/06/17 18:06:00

##新主机注册备份集rman target /
catalog start with '/tmp/backlv0_ORCL_20210617_107548592';
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete device type disk;##恢复表空间LUCIFER和系统表空间，指定时间点 `2021/06/17 18:06:00`run {
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';set until time '2021-06-17 18:06:00';
allocate channel ch01 device type disk;
allocate channel ch02 device type disk;
restore tablespace SYSTEM,SYSAUX,UNDOTBS1,USERS,LUCIFER;
release channel ch01;
release channel ch02;
}

The new host RECOVER DATABASE SKIP TABLESPACE is restored to the point in time 2021/06/17 18:06:00

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

There is a small bug here: the client environment is Windows, and an error is reported at the end of this step, and the database cannot be opened manually with the offline data file.

solution:

--将恢复跳过的表空间都offline drop掉，执行以下查询结果select 'alter database datafile '|| file_id ||' offline drop;' from dba_data_files where tablespace_name in ('LTEST','EXAMPLE');--再次开启数据库alter database open read only;

Note: If the missing archive log is displayed, you can refer to the following steps:

##查询恢复需要的归档日志号时间 alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"; 
select first_time,sequence# from v$archived_log where sequence#='7';##通过备份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进行恢复至指定时间点 2021-06-17 18:06:00 run { 
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; 
set until time '2021-06-17 18:06:00'; 
allocate channel ch01 device type disk; 
recover database skip tablespace LTEST,EXAMPLE; 
release channel ch01; 
}

The new host instance is turned on to read-only mode

sqlplus / as sysdbaalter database open read only;

Confirm that the table data of the new host instance is correct

select * from lucifer.lucifer;

Note: If it is not correct, repeat step 7 to adjust the time point slowly to 2021/06/17 18:10:00 to advance the recovery:

##关闭数据库sqlplus / as sysdba
shutdown immediate;##开启数据库到mount状态startup mount pfile='/tmp/pfile.ora';##重复 第7步，往前推进1分钟，调整时间点为 `2021/06/08 18:07:00`rman target /
run {
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';set until time '2021-06-17 18:07:00';
allocate channel ch01 device type disk;
recover database skip tablespace LTEST,EXAMPLE;
release channel ch01;
}

The main database creates a DBLINK connected to the new host instance, and retrieves table data from the new host instance through DBLINK

sqlplus / as sysdbaCREATE PUBLIC DATABASE LINK ORCL112CONNECT TO luciferIDENTIFIED BY luciferUSING '(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=10.211.55.112)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=orcl)
)
)
)';--通过dblink捞取数据create table lucifer.lucifer_0618 as select /*+full(lucifer)*/ * from lucifer.lu[email protected];select * from lucifer.lucifer_0618;

At this point, the entire RMAN recovery process is over.