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

Before 12c, the data loss caused by DDL (DROPTRUNCATE) and other operations is generally done through database incomplete recovery, tablespace-based time-point recovery (TSPITR) and flashback technology.

12C introduced rman time-point-based table recovery. The principle is as follows:

The new features of Recover Table are realized by creating an auxiliary temporary instance and a data pump tool. Usually two directories (AUXILIARY DESTINATION and DATAPUMP DESTINATION) should be prepared before Recover Table, AUXILIARY DESTINATION for temporary storage of secondary instance data files, and DATAPUMP DESTINATION for temporary storage of files exported by the data pump.

First, the testing process is as follows:

1.

SQL > set lines 60

SQL > desc tb

Name Null? Type

OWNER VARCHAR2 (128)

OBJECT_NAME VARCHAR2 (128)

SUBOBJECT_NAME VARCHAR2 (128)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2 (23)

CREATED DATE

LAST_DDL_TIME DATE

SQL > insert into tb select * from tb

72633 rows created.

SQL > commit

Commit complete.

SQL >

SQL > conn / as sysdba

Connected.

two。 Switch logs to write data to disk.

SQL > alter system switch logfile

3.rman backup database

Recovery Manager: Release 12.2.0.1.0-Production on Fri Jun 29 11:41:03 2018

Copyright (c) 1982, 2017, Oracle and/or itsaffiliates. All rights reserved.

Connected to target database: MISDB (DBID=4279250949)

RMAN > run

2 > {

3 > crosscheck archivelog all

4 > crosscheck backup

5 > delete noprompt obsolete

6 > delete noprompt expired backup

7 > allocate channel D1 type disk

8 > allocate channel D2 type disk

9 > backup format'/ u01/bk/%T_%d_%s_%p.dat'tag 'full_data' database

10 > backup format'/u01/bk/%T_%d_%s_%p.arc' tag 'full_arc' archivelog all delete all input

11 > backup format'/u01/bk/%T_%d_%s_%p.ctl' tag 'full_ctl' current controlfile

12 > release channel D1

13 > release channel D2

14 >}

Released channel: d1

Released channel: d2

RMAN >

SQL > select count (*) from rita.tb

COUNT (*)

-

145266

SQL > select current_scn from v$database

CURRENT_SCN

-

2074690

4. Delete tabl

SQL > drop table rita.tb purge

SQL > select count (*) from rita.tb

Selectcount (*) from rita.tb

*

ERROR at line 1:

ORA-00942: table or view does not exist

5. Build an auxiliary catalogue

[oracle@host01 U01] $mkdir recover/

[oracle@host01 U01] $mkdir dumpfiles

[oracle@host01 U01] $ls

App bk dumpfiles lost+found recover

[oracle@host01 U01] $cd dumpfiles/

[oracle@host01 dumpfiles] $ls

[oracle@host01 dumpfiles] $cd..

[oracle@host01 U01] $

6. View database backups:

List backup of database

Connected to target database: MISDB (DBID=4279250949)

RMAN >

Using target database control file instead ofrecovery catalog

7. Restore deleted tables:

Rman target /

Recovery Manager: Release 12.2.0.1.0-Production on Fri Jun 29 15:22:20 2018

Copyright (c) 1982, 2017, Oracle and/or itsaffiliates. All rights reserved.

Connected to target database: MISDB (DBID=4279250949)

RMAN > run {

2 > RECOVER TABLE rita.tb

3 > UNTIL SCN 2074690

4 > AUXILIARY DESTINATION'/ u01According to recover'

5 > datapump destination'/ u01bind dumpfiles'

6 >}

Starting recover at 29-JUN-18

Using target database control file instead ofrecovery catalog

Current log archived

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=52 instance=misdb1device type=DISK

RMAN-05026: warning: presuming following setof tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDOsegments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='ygBi' automatically create a database instance

Oracle@host01 datafile] $ps-ef | grep smon

Grid 5050 1 0 14:57? 00:00:00 asm_smon_+ASM1

Root 5144 1 1 14:58? 00:00:30/u01/app/12.2.0/grid/bin/osysmond.bin

Oracle 7730 1 0 15:00? 00:00:00 ora_smon_misdb1

Oracle 36939 1 015:28? 00:00:00 ora_smon_ygBi

Reating automatic instance, with SID='ygBi'

Initialization parameters used for automatic instance:

Db_name=MISDB

Db_unique_name=ygBi_pitr_MISDB

Compatible=12.2.0

Db_block_size=8192

Db_files=200

Diagnostic_dest=/u01/app/oracle

_ system_trig_enabled=FALSE

Sga_target=1728M

Processes=200

Db_create_file_dest=/u01/recover

Log_archive_dest_1='location=/u01/recover'-this space should be big enough

# No auxiliary parameter file used

Datafile 1 switched to datafile copy

Input datafile copy RECID=4 STAMP=980090756 filename=/u01/recover/MISDB/datafile/o1_mf_system_fmcqnsc8_.dbf

Datafile 4 switched to datafile copy

Input datafile copy RECID=5 STAMP=980090756 filename=/u01/recover/MISDB/datafile/o1_mf_undotbs1_fmcqpx2n_.dbf

Datafile 3 switched to datafile copy

Input datafile copy RECID=6 STAMP=980090756 filename=/u01/recover/MISDB/datafile/o1_mf_sysaux_fmcqpx17_.dbf

Contents of Memory Script:

{

# set requested point in time

Set until scn2074690

# online the datafiles restored or switched

Sql clone "alter database datafile 1 online"

Sql clone "alter database datafile 4 online"

Sql clone "alter database datafile 3 online"

# recover and open database read only

Recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX"

Sql clone 'alter database open read only'

. . .

Contents of Memory Script:

{

# create directory for datapump import

Sql "create or replace directory TSPITR_DIROBJ_DPDIRas''

/ u01Dumpfileskeeper'"

# create directory for datapump export

Sql clone "create or replace directoryTSPITR_DIROBJ_DPDIR as''

/ u01Dumpfileskeeper'"

}

Executing Memory Script

Sql statement: create or replace directoryTSPITR_DIROBJ_DPDIR as'/ u01max dumpfilescrafts'

Sql statement: create or replace directoryTSPITR_DIROBJ_DPDIR as'/ u01max dumpfilescrafts'

Performing export of tables...

EXPDP > Starting "SYS". "TSPITR_EXP_ygBi_gcph":

EXPDP > Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

EXPDP > Processing object typeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

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

EXPDP > Processing object type TABLE_EXPORT/TABLE/TABLE

EXPDP >. .exported "RITA". "TB" 19.21 MB 145266 rows

EXPDP > Mastertable "SYS". "TSPITR_EXP_ygBi_gcph" successfullyloaded/unloaded

EXPDP > *

EXPDP > Dumpfile set for SYS.TSPITR_EXP_ygBi_gcph is:

EXPDP > / u01/dumpfiles/tspitr_ygBi_13106.dmp

EXPDP > Job "SYS". "TSPITR_EXP_ygBi_gcph" successfully completed at FriJun 29 15:31:30 2018 elapsed 0 00:01:09

Export completed

Contents of Memory Script:

{

# shutdown clone before import

Shutdown clone abort

}

Executing Memory Script

Oracle instance shut down

Performing import of tables...

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

IMPDP > Starting "SYS". "TSPITR_IMP_ygBi_yzeq":

IMPDP > Processing object typeTABLE_EXPORT/TABLE/TABLE

IMPDP > Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

IMPDP >. .imported "RITA". "TB" 19.21 MB 145266 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_ygBi_yzeq" successfully completed at FriJun 29 15:32:44 2018 elapsed 0 00:00:41

Import completed

Second: as can be seen from the above example, the general process of recovery is

1.rman determines the backup based on the specified table.

2.rman restores a secondary instance based on the specified recover path to the specified point in time.

Creating automatic instance, with SID='ygBi'

Initialization parameters used for automatic instance:

Db_name=MISDB

Db_unique_name=ygBi_pitr_MISDB

Sql statement: alter database mount clone database

Sql statement: alter system archive log current

Contents of Memory Script:

{

# set requested point in time

Set until scn2074690

# set destinations for recovery set and auxiliary setdatafiles

Set newname for clone datafile 1 to new

Set newname for clone datafile 4 to new

Set newname for clone datafile 3 to new

Set newname for clone tempfile 1 to new

3. With the restored secondary instance, the database is exported to the production environment.

EXPDP > Starting "SYS". "TSPITR_EXP_ygBi_gcph":

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

Performing import of tables...

IMPDP > Mastertable "SYS". "TSPITR_IMP_ygBi_yzeq" successfullyloaded/unloaded

IMPDP > Starting "SYS". "TSPITR_IMP_ygBi_yzeq":

IMPDP > Processing object type TABLE_EXPORT/TABLE/TABLE

IMPDP > Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

IMPDP >. .imported "RITA". "TB" 19.21 MB 145266 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_ygBi_yzeq" successfully completed at FriJun 29 15:32:44 2018 elapsed 0 00:00:41

Import completed

4. Delete secondary instance

Recovery complete.

Three: several issues that RECOVER TABLE needs to pay attention to:

1. The target database must be placed in read-write mode.

two。 The target database must be placed in archive mode.

Tables or partitions under 3.SYS users cannot be restored.

4. Tables and partitions stored under SYSAUX and SYSTEM tablespaces cannot be restored.

Tables or table partitions on the 5.Standby database cannot be restored.

6. When you execute the "RECOVER TABLE" command, you can specify time at the following three levels as needed:

(1) SCN number

(2) Sequence number (log serial number)

(3) Time: specify the time according to the format in the NLS_LANG and NLS_DATE_FORMAT environment variables. You can also use SYSDATE, such as "SYSDATE-30", "to_date" ('2018-06-29 NLS_DATE_FORMAT 17-51-41) 48 colors.

Reference documentation

Https://docs.oracle.com/en/database/oracle/oracle-database/12.2/bradv/rman-recovering-tables-partitions.html#GUID-87B7F772-335F-4179-81C9-91678D026D01

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.