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

This article mainly introduces how to restore Oracle database backup, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to know about it.

The oracle database provides expdp and impdp commands for backing up and restoring the database.

For details, please refer to the oracle official document https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/database-utilities.pdf.

Backup and restore mainly include

FULL_MODE: backup and restore the entire database.

Schema Mode: default export mode, Schema mode.

Table Mode: table mode.

Tablespace Mode: tablespace schema.

Practice

Verification 1: back up the database data at a certain time, and the data can be restored to the backup time through the recovery statement.

Log in after switching users

[root@linuxtestb538] # su oraclebash-4.2$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0-Production on Tue Nov 23 14:40:45 2021Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0-ProductionVersion 19.3.0.0.0SQL >

Connect to the corresponding user

SQL > conn test/test@mypdbConnected.

Created the test_ tab table

Create table test_tab (id number (9) not null,title varchar2 (20))

Insert a piece of data

Insert into test_tab values (1 perfect hello world')

Export data file (launch database connection)

Expdp test/test@mypdb schemas=test dumpfile=test20211119_all.dmp logfile=20211119_all.dmp DIRECTORY=DATA_PUMP_DIR

Insert a piece of data

Insert into test_tab values (2 perfect hello test')

Currently, there are two pieces of data in the database, but there is only one piece of hello world data when the data is exported.

SQL > select * from test_tab; ID TITLE--1 hello world 2 hello test

Now we restore the database data through the impdp command

Bash-4.2$ impdp test/test@mypdb schemas=test DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211119_all.dmp logfile=20211119_recov.dmp;Import: Release 19.0.0.0.0-Production on Tue Nov 23 14:52:21 2021Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0-ProductionMaster table "TEST". "SYS_IMPORT_SCHEMA_01" successfully loaded/unloadedStarting "TEST". "SYS_IMPORT_SCHEMA_01": test/*@mypdb schemas=test DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211119_all.dmp logfile=20211119_recov.dmp Processing object type SCHEMA_EXPORT/USERORA-31684: Object type USER: "TEST" already existsProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEORA-39151: Table "TEST". "TEST_TAB" exists. All dependent metadata and data will be skipped due to table_exists_action of skipProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATAProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/STATISTICS/MARKERJob "TEST". "SYS_IMPORT_SCHEMA_01" completed with 2 error (s) at Tue Nov 23 14:52:37 2021 elapsed 0 00:00:14

We can see from the input that the test_ tab table already exists, so the relevant backup data is skipped, but our intention is to let the backup data overwrite the existing data regardless of whether the table already exists or not. Then we need to add the parameters of table_exists_action=replace.

Impdp test/test@mypdb schemas=test table_exists_action=replace DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211119_all.dmp logfile=20211119_recov.dmp;Import: Release 19.0.0.0.0-Production on Tue Nov 23 14:55:57 2021Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0-ProductionMaster table "TEST". "SYS_IMPORT_SCHEMA_01" successfully loaded/unloadedStarting "TEST". "SYS_IMPORT_SCHEMA_01": test/*@mypdb schemas=test table_exists_action=replace DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211119_all.dmp logfile=20211119_recov.dmp Processing object type SCHEMA_EXPORT/USERORA-31684: Object type USER: "TEST" already existsProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object Type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . Imported "TEST". "TEST_TAB" 5.539 KB 1 rowsProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/STATISTICS/MARKERJob "TEST". "SYS_IMPORT_SCHEMA_01" completed with 1 error (s) at Tue Nov 23 14:56:25 2021 elapsed 0 00:00:27

After connecting to the database, query the test_tab table and find that the data has been restored to only one hello world.

SQL > select * from test_tab; ID TITLE--1 hello world

Verification 2: when backing up data, you do not want to back up all tables, filter out some tables according to the conditions for backup, and restore only the backed-up table data.

Let's create another table at the beginning of his

Create table his_test_tab (id number (9) not null,title varchar2 (20))

Insert data

Insert into his_test_tab values (1 perfect hello world')

Export data

Bash-4.2$ expdp test/test@mypdb schemas=test dumpfile=test20211123-1_all.dmp logfile=20211123-1_all.dmp DIRECTORY=DATA_PUMP_DIR EXCLUDE=table:\ "like\ 'HIS%\'\"; Export: Release 19.0.0.0.0-Production on Tue Nov 23 15:16:39 2021Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0-ProductionStarting "TEST". "SYS_EXPORT_SCHEMA_01": test/*@mypdb schemas=test dumpfile=test20211123-1_all.dmp logfile=20211123-1_all.dmp DIRECTORY=DATA_PUMP_DIR EXCLUDE=table: "like 'HIS%'" Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATAProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing Object type SCHEMA_EXPORT/STATISTICS/MARKERProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX. . Exported "TEST". "TEST_TAB" 5.539 KB 1 rowsMaster table "TEST". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded**** * Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is: / opt/oracle/admin/ORCLCDB/dpdump/D0F96921D5E99512E0534390140A837F/test20211123-1_all.dmpJob "TEST". "SYS_EXPORT_SCHEMA_01" successfully completed at Tue Nov 23 15:17:39 2021 elapsed 0 00:01:00

Add data to test_tab and his_test_tab tables

SQL > insert into test_tab values (2) row created.SQL > insert into his_tab values (2) test'); insert into his_tab values (2) ERROR at line 1:ORA-00942: table or view does not existSQL > select * from test_tab ID TITLE--1 hello world 2 hello testSQL > select * from his_test_tab; ID TITLE--1 hello world 2 hello test

In test_tab and his_test_tab tables after inserting data

Restore data

Bash-4.2$ impdp test/test@mypdb schemas=test table_exists_action=replace DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211123-1_all.dmp logfile=20211123_recov.dmp;Import: Release 19.0.0.0.0-Production on Tue Nov 23 15:24:37 2021Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0-ProductionMaster table "TEST". "SYS_IMPORT_SCHEMA_01" successfully loaded/unloadedStarting "TEST". "SYS_IMPORT_SCHEMA_01": test/*@mypdb schemas=test table_exists_action=replace DIRECTORY=DATA_PUMP_DIR DUMPFILE=test20211123-1_all.dmp logfile=20211123_recov.dmp Processing object type SCHEMA_EXPORT/USERORA-31684: Object type USER: "TEST" already existsProcessing object type SCHEMA_EXPORT/SYSTEM_ GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . Imported "TEST". "TEST_TAB" 5.539 KB 1 rowsProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/STATISTICS/MARKERJob "TEST". "SYS_IMPORT_SCHEMA_01" completed with 1 error (s) at Tue Nov 23 15:24:47 2021 elapsed 0 00:00:09

Confirm the result

SQL > select * from his_test_tab; ID TITLE--1 hello world 2 hello testSQL > select * from test_tab; ID TITLE--1 hello world

As expected, the test_tab data was restored, but the his_test_tab data was not restored. You can also see from the backup log that we only backed up the data in the test_ tab table.

Thank you for reading this article carefully. I hope the article "how to restore Oracle database backup" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.