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

There are two main types of Oracle database backups: logical and physical. Each backup type has its own advantages and disadvantages. In previous versions, it was not feasible to restore tables or partitions with existing physical backups. A logical backup is required to restore a specific object.

In the Oracle12c version, you can restore a specific table or partition to a point in time or SCN from a RMAN backup in the event of a drop or truncate.

RMAN's table-level and table-partition-level recovery application scenarios:

1. You need to restore a very small number of tables to a specific point in time. In this case, TSPITR is not the most efficient solution because it moves all objects in the tablespace to a specified point in time.

2. You need to recover tables that have been logically corrupted or deleted and cleared.

3. Flashback Table is not available, for example, undo data has been overwritten.

4. Recover the data lost after the DDL operation modifies the table structure. Using the Flashback table is not possible because you run a DDL on the table between the desired point in time and the current time. Flashback tables cannot be reversed by structural changes such as truncated table operations.

Table-level and table-partition-level recovery limits for RMAN:

①: the SYS user table or partition cannot be restored

②: tables and partitions stored under SYSAUX and SYSTEM tablespaces cannot be recovered

③: restoring a table is not feasible when the REMAP option is used to restore a table that contains NOT NULL constraints

④: tables and partition tables on the repository cannot be restored

Table-level and table-partition-level recovery prerequisites for RMAN:

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

2 、 The target database must be in ARCHIVELOG mode.

3 、 You must have RMAN backups of the tables or table partitions as they existed at the point in time to which you want recover these objects.

4 、 To recover single table partitions, the COMPATIBLE initialization parameter for target database must be set to 11.1.0 or higher.

Recovery method:

1 、 SCN

2 、 Time

3 、 Sequence number

Steps when RMAN automatically processes recovery tables or table partitions from backups:

1. Determine which backups contain tables or table partitions that need to be restored, and restore them at a specified time.

two。 Determine if there is enough space on the target host to create a secondary instance that will be used during table or partition recovery. If there is not enough space required, RMAN will report an error and exit the restore operation.

3. Creates a secondary database and restores the specified table or table partition, and restores the specified table or table partition to the secondary database at the specified time. You can specify the name and location of the export dump file used to store the metadata of the recovered table or table partition.

4. Create a data pump export dump file that contains the restored table or table partition. You can specify the name and location of the export dump file used to store metadata for recovered tables or table partitions.

5. (optional) Import the data pump file produced in the previous step into the target instance. You can choose not to import export dump files that contain recovered tables or table partitions to the target database. If you do not import the export dump file as part of the recovery process, you must import it manually using impdp later.

6. (optional) rename restore tables or table partitions in the target database.

Prepare the test environment: Oracle12.2

[oracle@localhost] $sqlplus'/ as sysdba'SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 24 12:39:29 2017Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit ProductionSQL >

1. Restore the table in PDB

①: create a test table in pdb:

[oracle@localhost] $sqlplus admin/admin@192.168.2.100/testpdbSQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 21 16:28:24 2017Copyright (c) 1982, 2016, Oracle. All rights reserved.Last Successful login time: Fri Jul 21 2017 16:27:50 + 08:00Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit ProductionSQL > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';Session altered.SQL > select sysdate from dual;-View current time SYSDATE-2017-07-21 16:29:06SQL > create table emp as select * from dba_objects -create test table empTable created.SQL > select sysdate from dual;-View current time SYSDATE-2017-07-21 16:29:23SQL >

②: back up the entire library

[oracle@localhost] $rman target / Recovery Manager: Release 12.2.0.1.0-Production on Fri Jul 21 16:30:05 2017Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1476406003) RMAN > backup database format='/home/oracle/bak/fulldb_%d_%U' -this backup will back up CDB and all pdbStarting backup at 21-JUL-17using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=59 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile (s) in backup setinput datafile file number=00001 name=/opt/oracle/oradata/orcl/system01.dbfinput datafile file number=00003 name=/opt/oracle/oradata/orcl/sysaux01.dbfinput datafile file number=00013 name=/opt/oracle/oradata/orcl/system02 .dbfinput datafile file number=00014 name=/opt/oracle/oradata/orcl/sysaux02.dbfinput datafile file number=00004 name=/opt/oracle/oradata/orcl/undotbs01.dbf . Omit

③: log in to pdb and delete the test table

[oracle@localhost] $sqlplus admin/admin@192.168.2.100/testpdbSQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 21 16:35:15 2017Copyright (c) 1982, 2016, Oracle. All rights reserved.Last Successful login time: Fri Jul 21 2017 16:28:24 + 08:00Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit ProductionSQL > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';SQL > select sysdate from dual;-view current time SYSDATE-2017-07-21 16:35:56SQL > create table test_emp as select * from dba_objects -create a test table test_empSQL > select current_scn from vault database;-check the SCN number of the current database, which will be used to restore the use of CURRENT_SCN- 1664487SQL > commit; SQL > select sysdate from dual;SYSDATE-2017-07-21 16:37:04SQL > drop table emp. -delete the first test table just backed up empTable dropped.SQL > select sysdate from dual;SYSDATE-2017-07-21 16:38:31SQL > drop table test_emp;-delete the newly created test table test_emp (note that the table is not backed up) SQL > select current_scn from v$database -after deleting the table, check the SCNCURRENT_SCN- 1665210SQL > select current_scn from vault database of the current database; CURRENT_SCN- 1665213SQL >-Note: in the above operation, we can see that we have created a total of two tables, one table is backed up by emp, and the other test_emp is created after backup. We delete them all, and then restore them.

④: rman recovers tables in pdb

[oracle@localhost] $rman target / Recovery Manager: Release 12.2.0.1.0-Production on Fri Jul 21 17:32:24 2017Copyriht (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1476406003) RMAN > recover table admin.test_emp OF PLUGGABLE DATABASE testpdb until scn 1664487 auxiliary destination'/ opt/oracle/test' -because it is backing up the table in pdb So specify pdbauxiliary instance file/ opt/oracle/test/ORCL/539E6DBB875552F2E055000000000001/datafile/o1_mf_temp_dq3l21wf_.tmp deletedauxiliary instance file/ opt/oracle/test/ORCL/datafile/o1_mf_temp_dq3l1tmp_.tmp deletedauxiliary instance file/ opt/oracle/test/KSFR_PITR_TESTPDB_ORCL/onlinelog/o1_mf_3_dq3l4kg0_.log deletedauxiliary instance file/ opt/oracle/test/KSFR_PITR_TESTPDB_ORCL/onlinelog/o1_mf_2_dq3l47rl_.log deletedauxiliary instance file/ opt / oracle/test/KSFR_PITR_TESTPDB_ORCL/onlinelog/o1_mf_1_dq3l47nn_.log deletedauxiliary instance file/ opt/oracle/test/KSFR_PITR_TESTPDB_ORCL/539E6DBB875552F2E055000000000001/datafile/o1_mf_test_dq3l3xdf_.dbf deletedauxiliary instance file/ opt/oracle/test/ORCL/539E6DBB875552F2E055000000000001/datafile/o1_mf_sysaux_dq3l0sf7_.dbf deletedauxiliary instance file/ opt/oracle/test/ORCL/datafile/o1_mf_sysaux_dq3kyd0s_.dbf deletedauxiliary instance file/ opt/oracle/test/ORCL/datafile / o1_mf_sysaux_dq3kyd0g_.dbf deletedauxiliary instance file/ opt/oracle/test/ORCL/539E6DBB875552F2E055000000000001/datafile/o1_mf_undotbs1_dq3l0sg1_.dbf deletedauxiliary instance file/ opt/oracle/test/ORCL/datafile/o1_mf_undotbs1_dq3kyd0x_.dbf deletedauxiliary instance file/ opt/oracle/test/ORCL/539E6DBB875552F2E055000000000001/datafile/o1_mf_system_dq3l0sfc_.dbf deletedauxiliary instance file/ opt/oracle/test/ORCL/datafile/o1_mf_system_dq3kyd0m_.dbf deletedauxiliary instance file/ opt/oracle/test / ORCL/datafile/o1_mf_system_dq3kyd05_.dbf deletedauxiliary instance file/ opt/oracle/test/ORCL/controlfile/o1_mf_dq3ky1cb_.ctl deletedauxiliary instance file tspitr_ksFr_40802.dmp deletedFinished recover at 21murJULMAE 17-the log is omitted Even if the recovery is complete,-supplement RMAN > recover table admin.test_emp OF PLUGGABLE DATABASE testpdb until scn 1664487 auxiliary destination'/ opt/oracle/test' remap table admin.test_emp:test_empbak -rename the restored table

⑤: verify that the restore is complete:

SQL > select count (*) from emp; COUNT (*)-72633SQL > select count (*) from test_emp; COUNT (*)-72634

-from the query above, we can see that the recovery is complete and there is no problem. However, everyone must have a question, that is, why the table I did not back up has been successfully restored. If you have any doubt, you can check the official documents and learn more about the principle of recovery.

2. Oracle12C (table recovery without pdb level)

-PDB is not selected during the installation process, and Oracle12C is installed in the same way as the previous version:

The recovery process is the same as the above PDB-level restore, except that there are differences in the recovery commands, such as:

RMAN > recover table admin.emp until scn 1665210 remap table admin.emp:emp_bak auxiliary destination'/ hoem/oracle/bak'

Note:

①: the recovery method for partitioned tables is the same

②: even if the table emp does the DDL operation and modifies the table structure, it can still be restored in this way.

For more information on the Oracle12Crman recovery table, please see the official documentation:

Http://docs.oracle.com/database/121/BRADV/rcmresind.htm#BRADV686

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.