Database backup and recovery (learning notes), backup learning notes

Database backup and recovery (learning notes), backup learning notes

-- ********************** Data Pump Technology ------ oracle 10 Gb later appeared

-- Create a directory

Create directory dump_dir as 'e:/backup ';

-- Query all directories in the database

Select * from dba_directories;

-- Delete the database directory

Drop directory dump_dir;

Drop directory dump_file;

-- Create a tablespace

Create tablespace tbs_test

Datafile 'e:/t. dbf'

Size 10 M

Autoextend on;

-- Query table space

Select * from dba_tablespaces;

-- Create a test user

Create user tests identified by tests;

-- User authorization

Grant connect, resource to tests;

Revoke imp_full_database from scott;

 

-- Grant the user the permission to operate the dump_dir directory

Grant read, write on directory dump_dir to tests;

Grant read, write on directory dump_dir to scott;

--------------------------------- Backup (export )--------------

-- Export the entire database

Expdp help = y -- help File

-- Export

Expdp system/accp @ orcl directory = dump_dir dumpfile = full. bak full = y

-- Expdp Export

-- Directory specifies the directory

-- Dumpfile: Specifies the backup file name.

-- Full backup

-- Export tablespace

-- Query table space

Select * from scott.

Select * from dba_tablespaces;

-- Export all tablespaces

Expdp system/accp @ orcl directory = dump_dir dumpfile = tablespacefull. bak tablespaces

 

-- Export the specified tablespace tablespaces = tablespace table

Expdp system/accp @ orcl directory = dump_dir dumpfile = ts_test.bak tablespaces = tbs_test

-- Delete a tablespace

Drop tablespace tbs_test;

-- Export the user's SCHEMAS

-- Export scott user

Expdp scott/tiger @ orcl directory = dump_dir dumpfile = scottschma. bak schemas = scott

 

-- Export table

Expdp scott/tiger @ orcl directory = dump_dir dumpfile = scotttabs. bak tables = emp, dept, bonus, salgrade

-- Use Administrator

Expdp system/accp @ orcl directory = dump_dir dumpfile = scotttbales. bak tables = scott. emp, scott. dept, scott. bonus, scott. salgrade

-- Export a table

Expdp scott/tiger @ orcl directory = dump_dir dumpfile = scottemp. bak tables = emp

------------------------------ Restore (import )---------------

-- Import Keywords of impdd

 

-- Import the emp table, scottemp. bak File

Impdp scott/tiger @ orcl directory = dump_dir dumpfile = scottemp. bak tables = emp

 

-- Import scotttabs. bak to all tables under scott.

Impdp scott/tiger @ orcl directory = dump_dir dumpfile = scotttabs. bak tables = emp, dept, bonus, salgrade

-- Restore all g tables under scott to the tests user, scott

-- First, use dba to grant scott the imp_full_database permission.

-- Data Import Permission

Grant imp_full_database to scott;

-- Export Permission

Grant exp_full_database to scott;

Impdp scott/tiger @ orcl directory = dump_dir dumpfile = SCOTTTABS. BAK tables = emp, dept, bonus, salgrade remap_schema = scott: tests

-- Restores all g tables under scott to the tests user and uses the Administrator

Impdp system/accp @ orcl directory = dump_dir dumpfile = SCOTTTABS. BAK tables = scott. emp, scott. dept, scott. bonus, scott. salgrade remap_schema = scott: tests

 

-- Import scott user, use SCOTT user

Impdp scott/tiger @ orcl directory = dump_dir dumpfile = scottschma. bak schemas = scott

-- Import scott users and use the Administrator

Impdp system/accp @ orcl directory = dump_dir dumpfile = scottschma. bak schemas = scott

 

-- Import all objects in scott to tests

Impdp system/accp @ orcl directory = dump_dir dumpfile = scottschma. bak schemas = scott remap_schema = scott: tests

 

-- Import tablespace

Impdp system/accp @ orcl directory = dump_dir dumpfile = tablespacetbs_test.bak tablespaces = tbs_test

-- Import all tablespaces

Impdp system/accp @ orcl directory = dump_dir dumpfile = TABLESPACEFULL. BAK

-- Restore the entire database

Impdp system/accp @ orcl directory = dump_dir dumpfile = full. bak full = y

 

 

-- Use the exp/imp command ---------------- before running LR 10

Use exp/imp for backup

Exp help = y

-- Export the entire database

Exp system/accp @ orcl file = E:/bak/full. back full = y

-- Export a user

Exp scott/tiger @ orcl file = E:/bak/scott. back owner = scott

-- Export table

Exp scott/tiger @ orcl tables = (emp, dept) file = E:/bak/scotttabs. back

-- Export the USER tablespace

Exp system/accp @ orcl tablespaces = (users) file = E:/bak/tbs_users

 

--- Import

-- Import database

Imp system/accp @ orcl file = E:/bak/full. back full = y

-- Import table

Imp system/accp @ orcl file = E:/bak/scotttabs. back fromuser = scott touser = tests tables = (emp, dept)

Functional Differences Between exp/imp and expdp/impdp commands

1. Export the user usera object to user userb

Exp/imp usage

Fromuser = usera touser = userb;

Expdp/impdp usage

Remap_schema = usera: userb

2. Specify tables

Exp/imp usage:

Tables = (table1, table2 ,....)

Tables = (emp, dept );

Expdp/impdp usage

Tables = table1, table2 ,....

Tables = emp, dept

3. Change the tablespace

Expdp/impdp:

Remap_tablespace = old tablespace name: New tablespace name

4. Whether to export data rows

Exp ROWS = y, export data ROWS, ROWS = N, do not export data ROWS

Expdp content (ALL: Object + export data rows, DATA_ONLY: export only objects, METADATA_ONLY, export only data records)

 

Cold backup and Hot Backup

Cold backup occurs when the database is shut down normally. When the database is shut down normally, a complete database is provided.

For ORACLE, cold backup is the fastest and safest method.

1. Close the database completely

2. Back up data files of all databases

3. Restart the database

1) shut down the database

Sqlplus/nolog

Connect sys/orcl @ orcl as sysdba

Shutdown normal;

2) copy the data, use the copy command to back up all the data files in the ORCL database, redo the log files, control files, and initialize the parameter files to the specified directory.

Copy data files, redo log files, and control files

Host copy 'oracle main directory' \ oradate \ orcl e: \ back

Copy the initialization parameter file:

Host copy 'oracle main directory' \... \ initorcl. ora e: \ back

3) restart the database

Startup

Recovery steps

1) shut down the database

2) restore the backup data files, control files, and online redo logs.

3) Start the database

Hot Backup

Data backup and hot backup are performed when the database is running. They must be run in archive mode.

And requires a lot of disk space