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

What's New in Oracle 12C-RMAN recovery Table

How does oracle recover single table data?

Scenario 1: simple processing and fast recovery

1 if the table is delete, update, insert, and has been commit, the data can be quickly recovered by flashback if time and undo allow

Alter table t1 enable row movement

-based on Scn flashback

Flashback table t1 to scn 1726953

-based on time flashback

Flashback table T1 to timestamp to_timestamp ('2020-02-08 12 hh34:mi:ss')

Or save the data to another table through CTAS

Create table t1_recv as select * from T1 as of timestamp (systimestamp-interval'2' minute)

2 if the table is drop and the Recycle Bin is not emptied using purge, the deleted table can be recovered through the Recycle Bin (user_recyclebin)

-save the data from the Recycle Bin to another table through CTAS

Show recyclebin

Select object_name,ORIGINAL_NAME from user_recyclebin

Select original_name,operation,droptime from recyclebin

Create table T1 as select * from "BIN$HKCe4+VkDVXgU8UDqMAWvQ==$0"

-or flash the table directly.

Flashback table t1 to before drop

Scenario 2: restore through backup

Dealing with scenes with complex and slow recovery

1 if the table has been delete, update, insert, and has been commit, the time has elapsed and the undo information has been overwritten:

Or the table is emptied by truncate or drop and the Recycle Bin.

If you have an expdp or exp backup, you can restore it with a backup and you will lose data.

If there is a rman backup, before 12C, you can restore the backup to another database based on the point in time, and then export the table with lost data through expdp. When importing to the production database, the disadvantage is that the operation is tedious and the time is long.

Starting from 12C, recovering a single table through rman makes the operation command easier, but the recovery time is still longer.

Scenario 3: a scene that is difficult to deal with and may not be able to recover completely

The table has been misoperated (delete, update, truncate, drop) and there is no valid backup, the Recycle Bin has been emptied and the undo data has been overwritten:

You can consider tools such as bbed,ODU for data recovery.

This experiment simulates that a table in pdb is mistakenly delete and recovers a single table through rman.

The experimental process is as follows:

Check the basic information of the database

1 the database version is 19C (equivalent to 12.2.0.3 version)

SQL > select banner_full from v$version

BANNER_FULL

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0-Production

Version 19.3.0.0.0

Check and start the archive mode

SQL > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination / u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch

Oldest online log sequence 12

Current log sequence 14

[root@cjcos ~] # mkdir / arch

[root@cjcos ~] # chown oracle.oinstall / arch

SQL > alter system set log_archive_dest_1='location=/arch' scope=both

SQL > alter system set log_archive_format = "cjcpdb_arch_%t_%s_%r.arc" scope=spfile

SQL > shutdown immediate

SQL > startup mount

SQL > alter database archivelog

SQL > alter database open

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / arch

Oldest online log sequence 12

Next log sequence to archive 14

Current log sequence 14

SQL > alter system switch logfile

[oracle@cjcos ~] $cd / arch/

[oracle@cjcos arch] $ll-rth

Total 74M

-rw-r- 1 oracle oinstall 74m Feb 8 11:04 cjcpdb_arch_1_14_1030641846.arc

3. Create test data

SQL > conn sys/oracle@cjcpdb as sysdba

Connected.

SQL > select name from v$dbfile

NAME

/ u01/app/oracle/oradata/CJCDB/cjcpdb/system01.dbf

/ u01/app/oracle/oradata/CJCDB/cjcpdb/sysaux01.dbf

/ u01/app/oracle/oradata/CJCDB/cjcpdb/undotbs01.dbf

/ u01/app/oracle/oradata/CJCDB/cjcpdb/users01.dbf

SQL > create tablespace cjctbs datafile'/ u01 size autoextend on CJCDB size cjctbs01.dbf' oradata

SQL > create user cjc identified by cjc default tablespace cjctbs

SQL > grant connect,resource,dba to cjc

SQL > conn cjc/cjc@cjcpdb

SQL > create table T1 as select * from dba_objects

SQL > insert into T1 select* from T1

72397 rows created.

SQL > /

144794 rows created.

SQL > commit

SQL > update T1 set object_id=rownum

289588 rows updated.

SQL > commit

Commit complete.

Four: full database backup

[root@cjcos ~] # mkdir / backup/rman-p

[root@cjcos] # chown oracle.oinstall / backup-R

[oracle@cjcos ~] $rman target /

Recovery Manager: Release 19.0.0.0.0-Production on Sat Feb 8 11:21:00 2020

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to target database: CJCDB (DBID=3744777523)

RMAN >

Run

{

Allocate channel ch2 type disk

Allocate channel ch3 type disk

Backup database tag 'full' format' / backup/rman/%d_full_%T_%U.bak'

Sql 'alter system archive log current'

Backup archivelog all tag 'arch' format' / backup/rman/%d_arch_%T_%U.bak'

Backup current controlfile tag 'ctl' format' / backup/rman/%d_ctl_%T_%U.bak'

Release channel ch2

Release channel ch3

}

View backup information

RMAN > list backup of database

[oracle@cjcos rman] $pwd

/ backup/rman

[oracle@cjcos rman] $ll-rth

Total 2.4G

-rw-r- 1 oracle oinstall 415M Feb 8 11:48 CJCDB_full_20200208_13uo0vma_1_1.bak

-rw-r- 1 oracle oinstall 789M Feb 8 11:48 CJCDB_full_20200208_12uo0vma_1_1.bak

-rw-r- 1 oracle oinstall 272m Feb 8 11:49 CJCDB_full_20200208_15uo0vp0_1_1.bak

-rw-r- 1 oracle oinstall 302m Feb 8 11:49 CJCDB_full_20200208_14uo0vp0_1_1.bak

-rw-r- 1 oracle oinstall 305m Feb 8 11:50 CJCDB_full_20200208_16uo0vqd_1_1.bak

-rw-r- 1 oracle oinstall 252m Feb 8 11:50 CJCDB_full_20200208_17uo0vqe_1_1.bak

-rw-r- 1 oracle oinstall 6.3m Feb 8 11:50 CJCDB_arch_20200208_1auo0vrt_1_1.bak

-rw-r- 1 oracle oinstall 74m Feb 8 11:50 CJCDB_arch_20200208_19uo0vrt_1_1.bak

-rw-r- 1 oracle oinstall 125K Feb 8 11:50 CJCDB_arch_20200208_1buo0vs1_1_1.bak

-rw-r- 1 oracle oinstall 18m Feb 8 11:50 CJCDB_ctl_20200208_1cuo0vs2_1_1.bak

Five: check the current SCN and other information

SQL > conn cjc/cjc@cjcpdb

Connected.

SQL > show con_name user

CON_NAME

-

CJCPDB

USER is "CJC"

SQL > select count (*) from T1

COUNT (*)

-

289588

SQL > select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') from dual

TO_CHAR (SYSDATE,'YY

-

2020-02-08 11:53:51

SQL > select current_scn from v$database

CURRENT_SCN

-

2301721

Six: simulate erroneous deletion operation

SQL > delete T1 where object_id > 10

289578 rows deleted.

SQL > commit

Commit complete.

SQL > select count (*) from T1

COUNT (*)

-

ten

-for erroneous deletion at delete level, data can be quickly recovered by flashback if time and undo allow

SQL > create table T2 as select * from T1 as of timestamp (systimestamp-interval'3' minute)

Table created.

SQL > select count (*) from T2

COUNT (*)

-

289588

This case uses rman for T1 table recovery to the moment before the delete operation.

Seven: restore T1 table using rman backup

1 create a secondary database recovery directory / auxiliary/recover, and create an expdp export directory / auxiliary/dumpfiles

[root@cjcos ~] # mkdir / auxiliary/ {recover,dumpfiles}-p

[root@cjcos] # chown oracle.oinstall / auxiliary-R

[root@cjcos ~] # ll-rth / auxiliary/

Total 0

Drwxr-xr-x 2 oracle oinstall 6 Feb 8 12:14 recover

Drwxr-xr-x 2 oracle oinstall 6 Feb 8 12:14 dumpfiles

2 rman performs recovery operation

[oracle@cjcos ~] $rman target /

Recovery Manager: Release 19.0.0.0.0-Production on Sat Feb 8 12:15:53 2020

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to target database: CJCDB (DBID=3744777523)

RMAN >

Run {

RECOVER TABLE CJC.T1 OF PLUGGABLE DATABASE CJCPDB

UNTIL SCN 2301721

AUXILIARY DESTINATION'/ auxiliary/recover'

DATAPUMP DESTINATION'/ auxiliary/dumpfiles'

DUMP FILE 't1percent scnails 2301721.dmp'

REMAP TABLE 'CJC'.'T1':'T1_RECVR'

}

1 restore the T1 table under the CJC user under the CJCPDB database.

2Restoration to SCN 2301721 time.

3Restoration location / auxiliary/recover of the secondary instance created during the restore process.

4 backup location / auxiliary/dumpfiles exported through expdp during restore, backup name is t1_scn_2301721.dmp.

5 and insert the recovery data into the T1_RECVR table, verify that the data is all right, and then manually insert the T1_RECVR data back into T1.

The following is part of the information in the process of interception and recovery, all of which is done automatically without human intervention.

.

-1 create a secondary instance DiFo and restore the data

Creating automatic instance, with SID='DiFo'

Initialization parameters used for automatic instance:

Db_name=CJCDB

Db_unique_name=DiFo_pitr_CJCPDB_CJCDB

Compatible=19.0.0

Db_block_size=8192

Db_files=200

Diagnostic_dest=/u01/app/oracle

_ system_trig_enabled=FALSE

Sga_target=1104M

Processes=200

Db_create_file_dest=/auxiliary/recover

Log_archive_dest_1='location=/auxiliary/recover'

Enable_pluggable_database=true

_ clone_one_pdb_recovery=true

# No auxiliary parameter file used

Starting up automatic instance CJCDB

.

-2 recovery based on scn

Contents of Memory Script:

{

# set requested point in time

Set until scn 2301721

# online the datafiles restored or switched

Sql clone 'CJCPDB' "alter database datafile 13 online"

# recover and open resetlogs

Recover clone database tablespace "CJCPDB": "CJCTBS", "SYSTEM", "CJCPDB": "SYSTEM", "UNDOTBS1", "CJCPDB": "UNDOTBS1", "SYSAUX", "CJCPDB": "SYSAUX" delete archivelog

Alter clone database open resetlogs

}

Executing Memory Script

.

-3 expdp exports T1 table data to / auxiliary/dumpfiles/t1_scn_2301721.dmp

Contents of Memory Script:

{

# create directory for datapump import

Sql 'CJCPDB' "create or replace directory

TSPITR_DIROBJ_DPDIR as''

/ auxiliary/dumpfiles'' "

# create directory for datapump export

Sql clone 'CJCPDB' "create or replace directory

TSPITR_DIROBJ_DPDIR as''

/ auxiliary/dumpfiles'' "

}

Executing Memory Script

.

Performing export of tables...

EXPDP > Starting "SYS". "TSPITR_EXP_DiFo_aBzr":

EXPDP > Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

EXPDP > Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

EXPDP > Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

EXPDP > Processing object type TABLE_EXPORT/TABLE/TABLE

EXPDP >. . Exported "CJC". "T1" 38.18 MB 289588 rows

EXPDP > Master table "SYS". "TSPITR_EXP_DiFo_aBzr" successfully loaded/unloaded

EXPDP > *

EXPDP > Dump file set for SYS.TSPITR_EXP_DiFo_aBzr is:

EXPDP > / auxiliary/dumpfiles/t1_scn_2301721.dmp

EXPDP > Job "SYS". "TSPITR_EXP_DiFo_aBzr" successfully completed at Sat Feb 8 12:32:57 2020 elapsed 0 00:01:45

Export completed

.

-4 impdp imports T1 data into the formal library T1_RECVR table

Performing import of tables...

IMPDP > Master table "SYS". "TSPITR_IMP_DiFo_BABf" successfully loaded/unloaded

IMPDP > Starting "SYS". "TSPITR_IMP_DiFo_BABf":

IMPDP > Processing object type TABLE_EXPORT/TABLE/TABLE

IMPDP > Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

IMPDP >. . Imported "CJC". "T1_RECVR" 38.18 MB 289588 rows

IMPDP > Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

IMPDP > Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

IMPDP > Job "SYS". "TSPITR_IMP_DiFo_BABf" successfully completed at Sat Feb 8 12:34:17 2020 elapsed 0 00:00:49

Import completed

Eight: check that the data has been restored to t1_recvr

-after comparing the data, you can choose to insert it back into T1 manually.

SQL > conn cjc/cjc@cjcpdb

SQL > select count (*) from T1

COUNT (*)

-

ten

SQL > select count (*) from t1_recvr

COUNT (*)

-

289588

For more information, please refer to the official documentation:

Https://docs.oracle.com/en/database/oracle/oracle-database/12.2/bradv/rman-recovering-tables-partitions.html#GUID-04D1B794-25D0-4C1E-9A98-F08AD361B41E

Database Backup and Recovery User's Guide22.1.3 Basic Concepts of Recovering Tables and Table Partitions from RMAN Backups

.

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

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.