Shulou(Shulou.com)06/01 Report--

In many cases, human misoperation will affect the data of Oracle, such as mistakenly deleting data. When a misoperation occurs, there are usually four solutions:

1. Flashback database or table

two。 Backup and recovery

3.logminer mining redo

4. Use the delay library

None of the first three methods is perfect:

Flashback database has a great impact, and flashback on the library needs to be turned on in advance.

Flashback tables or flashback version queries are easy to use, but it is easy to take snapshots of old in busy databases.

Backup recovery takes a long time and has a great impact.

Logminer mining logs takes time and effort.

At this time, if you build a delay repository in advance, you can easily get the mistakenly deleted data from the slave database. The following is a demonstration.

Main library:

SQL > show parameter instance_name

NAME TYPE VALUE

-

Instance_name string BDDEV2

Prepare the library:

SQL > show parameter instance_name

NAME TYPE VALUE

-

Instance_name string BDTEST

The available tables and data are as follows:

SQL > select * from scott.tb_test

ID AGE TM

12 20181203 14:22:14

2 3 20181203 14:22:14

3 4 20181203 14:22:14

4 5 20181203 14:22:14

5 6 20181203 14:22:14

6 7 20181203 14:22:14

7 8 20181203 14:22:14

8 9 20181203 14:22:14

9 10 20181203 14:22:14

10 11 20181203 14:22:14

10 rows selected.

At this point, the slave database is applied in real time:

SQL > SELECT * FROM V$DATAGUARD_STATS

NAME VALUE UNIT TIME_COMPUTED DATUM_TIME

Transport lag + 00 00:00:00 day (2) to second (0) interval 12 max 03max 2018 14:23:33 12pm 03max 2018 14:23:33

Apply lag + 00 00:00:00 day (2) to second (0) interval 12 max 03max 2018 14:23:33 12pm 03max 2018 14:23:33

Apply finish time + 00 00 to second 00.000 day (2) to second (3) interval 12 pick 03 pick 2018 14:23:33

Estimated startup time 7 second 12/03/2018 14:23:33

The standby library is converted to a delayed library with a delay of 60 minutes:

SQL > alter database recover managed standby database cancel

Database altered.

SQL > alter database recover managed standby database disconnect from session delay 60

Database altered.

Master library modification record:

SQL > update scott.tb_test set age = 100 dint tm = sysdate where id = 10

1 row updated.

SQL > commit

Commit complete.

SQL > select * from scott.tb_test

ID AGE TM

12 20181203 14:22:14

2 3 20181203 14:22:14

3 4 20181203 14:22:14

4 5 20181203 14:22:14

5 6 20181203 14:22:14

6 7 20181203 14:22:14

7 8 20181203 14:22:14

8 9 20181203 14:22:14

9 10 20181203 14:22:14

10 100 20181203 14:26:50

10 rows selected.

As you can see, the time to modify the data is' 20181203 14:26:50'.

Delete the table data at this point:

SQL > select sysdate from dual

SYSDATE

-

20181203 14:28:18

SQL > truncate table scott.tb_test

Table truncated.

SQL > alter system switch logfile

System altered.

At this point, the data in the master database has been deleted, and the data in the slave database remains before the master database modifies the data:

SQL > select * from scott.tb_test

ID AGE TM

12 20181203 14:22:14

2 3 20181203 14:22:14

3 4 20181203 14:22:14

4 5 20181203 14:22:14

5 6 20181203 14:22:14

6 7 20181203 14:22:14

7 8 20181203 14:22:14

8 9 20181203 14:22:14

9 10 20181203 14:22:14

10 11 20181203 14:22:14

If we want to recover the data after update and before truncate, we need to manually roll forward the repository to the specified point in time:

-- prepare the database operation

Restart the slave library to mount:

SQL > startup force mount

ORACLE instance started.

Total System Global Area 2.1379E+10 bytes

Fixed Size 2262656 bytes

Variable Size 2.0401E+10 bytes

Database Buffers 939524096 bytes

Redo Buffers 36073472 bytes

Database mounted.

Manually roll forward the library to the specified point in time:

SQL > recover standby database until time '20181203 14 2715 00'

ORA-00279: change 108456269 generated at 12/03/2018 14:26:50 needed for thread 1

ORA-00289: suggestion: / opt/app/oracle/archivelog_bdtest/1_248_987097214.dbf

ORA-00280: change 108456269 for thread 1 is in sequence # 248

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

Log applied.

Media recovery complete.

Roll forward is complete, open the library:

SQL > alter database open

Database altered.

SQL > alter session set nls_date_format='yyyymmdd hh34:mi:ss'

Session altered.

SQL > select * from scott.tb_test

ID AGE TM

12 20181203 14:22:14

2 3 20181203 14:22:14

3 4 20181203 14:22:14

4 5 20181203 14:22:14

5 6 20181203 14:22:14

6 7 20181203 14:22:14

7 8 20181203 14:22:14

8 9 20181203 14:22:14

9 10 20181203 14:22:14

10 100 20181203 14:26:50

10 rows selected.

At this point, you have the required data, and you just need to import the data back into the main library.

When the processing is complete, re-open the redo application of the repository:

SQL > alter database recover managed standby database disconnect from session delay 60

Database altered.

At this time, the standby library has become a normal delay library.

Note:

1. DELAY time for preparing the database refers to the time when the log of the master database is archived, and then the application of the log is delayed at the time specified by DELAY. For example, if the DELAY parameter is set to 20 minutes, and the log of the master database is archived at 10:00, the archived log will not be applied until 10:20.

two。 You can also specify the DELAY parameter in LOG_ARCHIVE_DEST_2 to achieve the same delay:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DEVSTBY LGWR ASYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DEVSTBY DELAY=60'

3. The slave database cannot be applied in real time, because the real-time application will invalidate the DELAY parameters and turn the standby database into ADG.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.