Database backup and Recovery (Learning Note learning)

--********************** Data pump technology appears after------Oracle 10G

--Create a directory

Create directory Dump_dir as ' E:/backup ';

--Querying the database for all directories

SELECT * from Dba_directories;

--Delete Database directory

Drop directory Dump_dir;

Drop directory Dump_file;

--Create TABLE space

Create Tablespace Tbs_test

DataFile ' e:/t.dbf '

Size 10M

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 user permission to manipulate 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/[email protected] Directory=dump_dir Dumpfile=full.bak full=y

--EXPDP Export

--directory the specified directory

--dumpfile Specify the file name of the backup

--full Full Backup

--Export Table space

--Query table space

SELECT * from Scott.

SELECT * from Dba_tablespaces;

--Export all tablespace tablespaces table space

EXPDP System/[email protected] Directory=dump_dir Dumpfile=tablespacefull.bak tablespaces

--Export the specified Tablespace tablespaces= table space table

EXPDP System/[email protected] Directory=dump_dir Dumpfile=ts_test.bak tablespaces=tbs_test

--Delete Table space

Drop Tablespace tbs_test;

--Export user schemas

--Export Scott Users

EXPDP Scott/[email protected] Directory=dump_dir Dumpfile=scottschma.bak Schemas=scott

--Export table

EXPDP Scott/[email protected] Directory=dump_dir Dumpfile=scotttabs.bak Tables=emp,dept,bonus,salgrade

--Using administrator

EXPDP System/[email protected] Directory=dump_dir Dumpfile=scotttbales.bak Tables=scott.emp,scott.dept,scott.bonus, Scott.salgrade

--Export a table

EXPDP Scott/[email protected] Directory=dump_dir Dumpfile=scottemp.bak tables=emp

------------------------------Recovery (Import)---------------

--IMPDD Import Keywords

--Import EMP table, Scottemp.bak file

IMPDP Scott/[email protected] Directory=dump_dir Dumpfile=scottemp.bak tables=emp

--Import all the tables under Scott Scotttabs.bak

IMPDP Scott/[email protected] Directory=dump_dir Dumpfile=scotttabs.bak Tables=emp,dept,bonus,salgrade

--Restore all G tables under Scott to tests users, Scott

--First Use DBA for Scott Privilege Imp_full_database

--Import data permissions

Grant Imp_full_database to Scott;

--Export permissions

Grant Exp_full_database to Scott;

IMPDP Scott/[email protected] directory=dump_dir dumpfile=scotttabs. BAK Tables=emp,dept,bonus,salgrade remap_schema=scott:tests

--Restore all G tables under Scott to the tests user, using the administrator

IMPDP System/[email protected] directory=dump_dir dumpfile=scotttabs. BAK Tables=scott.emp,scott.dept,scott.bonus,scott.salgrade remap_schema=scott:tests

--Import Scott users, with Scott users

IMPDP Scott/[email protected] Directory=dump_dir Dumpfile=scottschma.bak Schemas=scott

--Import Scott users, use Administrator

IMPDP System/[email protected] Directory=dump_dir Dumpfile=scottschma.bak Schemas=scott

--Import all the objects in Scott into tests

IMPDP System/[email protected] Directory=dump_dir Dumpfile=scottschma.bak Schemas=scott remap_schema=scott:tests

--Import Table space

IMPDP System/[email protected] Directory=dump_dir Dumpfile=tablespacetbs_test.bak tablespaces=tbs_test

--Import all table spaces

IMPDP System/[email protected] directory=dump_dir dumpfile=tablespacefull. Bak

--Restore the entire database

IMPDP System/[email protected] Directory=dump_dir Dumpfile=full.bak full=y

--Before using the Exp/imp command------------------ORACLR 10

Using Exp/imp Backup

Exp help=y

--Export the entire database

Exp System/[email protected] File=e:/bak/full.back full=y

--Export user

Exp Scott/[email protected] File=e:/bak/scott.back Owner=scott

--Export table

Exp Scott/[email protected] tables= (emp,dept) file=e:/bak/scotttabs.back

--Export user table space

Exp System/[email protected] tablespaces= (users) file=e:/bak/tbs_users

---Import

--Import Database

Imp system/[email protected] File=e:/bak/full.back full=y

--Import Table

Imp system/[email protected] File=e:/bak/scotttabs.back Fromuser=scott touser=tests tables= (emp,dept)

The functional differences between the exp/imp and EXPDP/IMPDP commands

1. The user UserA the object to the user UserB

Exp/imp usage

Fromuser=usera Touser=userb;

EXPDP/IMPDP usage

Remap_schema=usera:userb

2. Specify some tables

Exp/imp usage:

tables= (Table1,table2,....)

Tables= (emp,dept);

EXPDP/IMPDP usage

Tables=table1,table2,....

Tables=emp,dept

3. Replace the table space

EXPDP/IMPDP:

remap_tablespace= old tablespace name: New table space Name

4. Do you want to export data rows

Exp rows=y, exporting data rows, rows=n, not exporting data rows

EXPDP content (All: Object + Export Data rows, Data_only: Export only objects, metadata_only, export data records only)

Cold and hot Backup

A cold backup occurs when the database has been shut down properly and provides us with a complete database when it shuts down normally

Cold backup is the fastest and safest method for Oracle

1 shutting down the database completely

2. Backing up data files for all databases

3. Restart the database

1) Close the database

Sqlplus/nolog

Connect Sys/[email protected] as Sysdba

Shutdown normal;

2) Copy data, use Copy command to back up all data files of ORCL database, redo log file, control file, initialize parameter file to specified directory

Copy data files, redo log files, control files

Host copy ' Oracle home directory ' \ORADATE\ORCL e:\back

Copy initialization parameter file:

Host copy ' Oracle home directory ' \...\initorcl.ora e:\back

3) Restart the database

Startup

Recovery steps

1) Close the database

2) Restore the backed up data files, control files, and online redo logs

3) Start the database

Hot backup

is run under the database, data backup, hot backup need to run in the database archive mode

and requires a lot of disk space

Database backup and Recovery (Learning Note learning)