On Oracle logical Backup, data pump backup and cold backup

Logical backup (Data migration):
Backup in Logical Structure
Move data across users
Moving databases across databases
Save the original data state for the test
Version upgrade of a database

Considerations for Logical Export:
EXP program in the directory found the same name file will be directly overwritten, do not prompt!!
Exp cannot back up empty table with no segments
Be sure to note the character set when performing logical export! It is best to use a small table containing Chinese to do the test!!
The data at the time of import is exactly the same as the data at the time of export, and the data changes of the tables in the database are lost!!

Logical export: All versions available, both server side and client available

Mkdir-p/home/oracle/expbk

Sql> CREATE TABLE scott.t01 as SELECT * from Dba_objects; Table created. elapsed:00:00:00.32

Sql> Select COUNT (*) from scott.t01; COUNT (*)----------86259elapsed:00:00:00.01

(1) Backup sheet: Generate backup path and log directory
Exp Userid=scott/tiger tables=t01 file=/home/oracle/expbk/t01.dmp buffer=1048576 feedback=10000 log=/home/oracle/ Expbk/t01.log

[Email protected] expbk]$ lltotal 9748-rw-r--r--1 Oracle oinstall 9977856 SEP-13:25 t01.dmp-rw-r--r--1 Oracle Oinsta LL 552 Sep 13:25 T01.log

Logical Import:
drop table t01 Purge;

sql> drop table scott.t01 purge; Table dropped. elapsed:00:00:00.11

Sql> SELECT * FROM Scott.t01;select * from scott.t01 *error to line 1:ora-00942:table or view does Not existelapsed:00:00:00.00

Imp userid=scott/tiger tables=t01 file=/home/oracle/expbk/t01.dmp buffer=1048576 feedback=10000 log=/home/oracle/ Expbk/t01.log

Sql> Select COUNT (*) from scott.t01; COUNT (*)----------86259elapsed:00:00:00.00

(2) Remote import via the network:
Environment: Prepare 2 Oracle Servers
The network configuration is as follows:
Vim/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.201.xx.75) (PORT = 1521)) (Connect_data = (SERVER = D edicated) (service_name = ORCL)) = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.201.xx.74) (PORT = 1521)) (Connect_data = (SERVER = dedicated) (service_name = ORCL)))

#####################
Vim/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

LISTENER = (Description_list = (DESCRIPTION = (address = (PROTOCOL = IPC) (KEY = EXTPROC1521)) (address = (PR Otocol = TCP) (HOST = s74sit) (PORT = 1521)))

Connected to 75, it is obvious that S75sit does not have this table scott.t01, as follows:

 [[email protected] ~]$ sqlplus scott/[email protected] SQL*Plus:  Release 11.2.0.4.0 production on mon sep 26 14:19:17 2016copyright   (c)  1982, 2013, oracle.  all rights reserved. connected to:oracle database 11g enterprise edition release 11.2.0.4.0  - 64bit productionwith the partitioning, olap, data mining and  real application testing optionssql> select count (*)  from  Scott.t01;select count (*)  from scott.t01                            * Error at line 1:ora-00942: table or view does not existelapsed:  00:00:00.00SQL>

Remote Import to 75 this server
Imp userid=scott/[email protected] tables=t01 file=/home/oracle/expbk/t01.dmp buffer=1048576 feedback=10000 log=/home /oracle/expbk/t01.log

In the View

Sql> Select COUNT (*) from scott.t01; COUNT (*)----------86259elapsed:00:00:00.00

It worked!!!!!!!!

(3) When exporting data with query criteria: SELECT * from Scott.emp where deptno=30;
Exp Scott/tiger tables=emp file=/home/oracle/expbk/emp_30.dmp query=\ ' where deptno=30\ ' buffer=1000000 log=/home/ Oracle/expbk/emp_30.log

(4) Append data when importing: Ignore=y
Imp scott/tiger tables=emp file=/home/oracle/expbk/emp_30.dmp ignore=y buffer=1000000 log=/home/oracle/expbk/imp_emp _30.log

(5) Show import process only, do not import operation: show=y
Imp scott/tiger tables=emp file=/home/oracle/expbk/emp_30.dmp ignore=y buffer=1000000 log=/home/oracle/expbk/imp_emp _30.log show=y

(6) Flash back export (dependent on undo data): System
Exp system/uplooking tables=scott.emp file=/home/oracle/expbk/emp_1040.dmp buffer=1000000 flashback_time=\ "to_ Timestamp\ (\ ' 2016-09-24 10:45:00\ ', \ ' yyyy-mm-dd hh24:mi:ss\ ' \) \ "Log=/home/oracle/expbk/emp_1040.log

(7) Full backup import data:
Imp system/uplooking file=/home/oracle/expbk/e03_1015.dmp full=y ignore=y show=y

Export only table structure (metadata) do not export data: rows=n backup model, do not back up data
Exp Scott/tiger tables=ob1 rows=n file=/home/oracle/expbk/ob1_metadata.dmp log=/home/oracle/expbk/ob1_metadata.log

(8) Write script scheduled backup

vi/home/oracle/expbk/exp.sh

Export Oracle_home=/u01/app/oracle/product/11.2.0/db_1export oracle_sid=orclexport lang=zh_cn.utf8export NLS_LANG= American_america. We8mswin1252name= ' Date ' +%y%m%d_%h%m%s ' $ORACLE _home/bin/exp userid=scott/tiger tables=ob1 file=/home/oracle/ Expbk/ob1\_$name.dmp buffer=1048576 feedback=10000 Log=/home/oracle/expbk/ob1\_$name.log '

chmod +x/home/oracle/expbk/exp.sh

(9) Export User: Owner=scott
Exp Userid=scott/tiger Owner=scott file=/home/oracle/expbk/scott.dmp buffer=1048576 feedback=10000 log=/home/oracle/ Expbk/scott.log
Import User: When importing user data, create a user in the database!
Drop user Scott Cascade;

Create user Scott identified by Tiger;
Imp userid=scott/tiger full=y file=/home/oracle/expbk/scott.dmp buffer=1048576 feedback=10000 log=/home/oracle/expbk /impscott.log

(10) Import data across users: Scott-->tom
Imp userid=system/uplooking file=scott.dmp fromuser=scott touser=tom tables=dept,emp,salgrade buffer=1000000 log= Imptom.log

(11) Compressing the backup file using the host pipeline:
Mknod/home/oracle/expbk/exp_pipe P
Exp Userid=scott/tiger Owner=scott log=/home/oracle/expbk/scott.log file=/home/oracle/expbk/exp_pipe & gzip </ Home/oracle/expbk/exp_pipe> scott02.dmp.gz

(12) Export table space:
Exp system/uplooking tablespaces=data01 file=/home/oracle/expbk/exp_data01.dmp buffer=1000000 log=/home/oracle/ Expbk/exp_data01.log
Import Tablespace: When the original table space is imported, the table is imported to the user's default persistent tablespace if it does not exist
Imp system/uplooking full=y file=/home/oracle/expbk/exp_data01.dmp buffer=1000000 log=/home/oracle/expbk/imp_ Data01.log

(13) Transport Tablespace mode: Database version must be consistent, character set must be consistent
Create tablespace teach10 datafile '/home/oracle/teach10.dbf ' size 10m;
CREATE table BLAKE.CRM (x int) tablespace teach10;
INSERT into BLAKE.CRM values (1990);
Commit
Table space needs to be in read-only mode
Alter Tablespace TEACH10 Read only;
Export Table Space Metadata
EXP \ ' sys/uplooking as Sysdba\ ' tablespaces=teach10 transport_tablespace=y file=/home/oracle/expbk/teach10.dmp log=/ Home/oracle/expbk/teach10.log
Uploading data files to remote
scp/home/oracle/teach10.dbf [Email protected]:/u01/app/oracle/oradata/zccdb/
Create user
Sqlplus Sys/[email protected] as Sysdba
Grant Connect,resource to Blake identified by Blake;
Import metadata to a remote database
IMP \ ' Sys/[email protected] as Sysdba\ ' Tablespaces=teach10 transport_tablespace=y file=/home/oracle/expbk/ Teach10.dmp datafiles=\ '/u01/app/oracle/oradata/zccdb/teach10.dbf\ ' Log=/home/oracle/expbk/imp_teach10.log
Table Space Read write:
Alter Tablespace TEACH10 READ write;

(14) Full-Library mode:
Exp system/uplooking full=y file=full.dmp buffer=10000000 log=full.log

CREATE TABLE Scott.t2
(
ID Number (10),
name1 VARCHAR2 (15),
Name2 VARCHAR2 (15),
Name3 VARCHAR2 (15),
Name4 VARCHAR2 (15),
Name5 VARCHAR2 (15),
Name6 VARCHAR2 (15),
Name7 VARCHAR2 (15),
Name8 VARCHAR2 (15),
Name9 VARCHAR2 (15),
Name10 VARCHAR2 (15),
Name11 VARCHAR2 (15),
Name12 VARCHAR2 (15),
Name13 VARCHAR2 (15),
Name14 VARCHAR2 (15),
NAME15 VARCHAR2 (15),
Name16 VARCHAR2 (15),
Name17 VARCHAR2 (15),
Name18 VARCHAR2 (15),
Name19 VARCHAR2 (15),
Name20 VARCHAR2 (15),
NAME21 VARCHAR2 (15),
Name22 VARCHAR2 (15),
Name23 VARCHAR2 (15),
Name24 VARCHAR2 (15),
Name25 VARCHAR2 (15),
Name26 VARCHAR2 (15),
Name27 VARCHAR2 (15),
Name28 VARCHAR2 (15),
Name29 VARCHAR2 (15),
Name30 VARCHAR2 (15),
Name31 VARCHAR2 (15),
Name32 VARCHAR2 (15),
Name33 VARCHAR2 (15),
Name34 VARCHAR2 (15),
Name35 VARCHAR2 (15),
Name36 VARCHAR2 (15),
Name37 VARCHAR2 (15),
Name38 VARCHAR2 (15),
Name39 VARCHAR2 (15),
Name40 VARCHAR2 (15),
Name41 VARCHAR2 (15),
Name42 VARCHAR2 (15),
Name43 VARCHAR2 (15),
Name44 VARCHAR2 (15),
Name45 VARCHAR2 (15),
Name46 VARCHAR2 (15),
Name47 VARCHAR2 (15),
Name48 VARCHAR2 (15),
name49 VARCHAR2 (15),
Name50 VARCHAR2 (15));

Begin
For I in 1..100000 loop
Insert into SCOTT.T2 (id,name1) VALUES (i, ' A ');
End Loop;
Commit
End
/

##########################################################################################
Data pump: Versions available after Oracle 10g, available only on the server
1. Create a logical directory and use a logical directory to save the data pump's backup files
Conn/as SYSDBA
SELECT * from All_directories;
Create or replace directory expbk as '/home/oracle/expbk ';

2. Grant Scott users permission to read and write to the logical directory
Grant Read,write on directory expbk to Scott;

3. Backing up data using a data pump

Back up metadata information only
EXPDP scott/tiger job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1_metadata.dmp content=metadata_only logfile =ob1_metadata.log

Data-only Information
EXPDP scott/tiger job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1_data.dmp content=data_only logfile=ob1_ Data.log

Both backup
EXPDP scott/tiger job_name=exp_ob1 directory=expbk tables=ob1 dumpfile=ob1_all.dmp content=all logfile=ob1_all.log


IMPDP Scott/tiger directory=expbk dumpfile=ob1.dmp logfile=imp_ob1.log

EXPDP scott/tiger directory=expbk dumpfile=scott.dmp parallel=8 job_name=scott_job Schemas=scott content=all logfile= Scott.log

EXPDP system/oracle directory=expbk dumpfile=users.dmp parallel=8 job_name=users_job tablespaces=users content=all Logfile=users.log
~~~~~~~~~~~~
Alter Tablespace TEACH10 Read only;
EXPDP \ ' sys/oracle as Sysdba\ ' directory=expbk dumpfile=teach10.dmp tablespaces=teach10 transport_tablespace=y logfile =teach10.log

SCP teach10.dmp [Email protected]:/home/oracle/
scp/home/oracle/teach10.dbf [Email protected]:/demo/teach10.dbf

Create or replace directory expbk as '/home/oracle ';

IMPDP \ ' sys/oracle as Sysdba\ ' directory=expbk dumpfile=teach10.dmp logfile=impteach10.log transport_datafiles=\ '/ Demo/teach10.dbf\ '
Alter Tablespace TEACH10 Read write;
Alter Tablespace TEACH10 Read write;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Data pump backup full-Library mode:

EXPDP system/oracle directory=expbk dumpfile=orcl.dmp parallel=8 job_name=orcl_job full=y logfile=orcl.log

EXPDP scott/tiger job_name=exp_e directory=expbk include=table:\ "like \ ' e%\ ' \" Dumpfile=e.dmp logfile=e.log

EXPDP scott/tiger job_name=exp_e directory=expbk include=view dumpfile=v.dmp
EXPDP scott/tiger job_name=exp_e directory=expbk include=procedure,package,function dumpfile=p.dmp

EXPDP scott/tiger job_name=exp_e directory=expbk exclude=table:\ "like \ ' e%\ ' \" Dumpfile=scott_not_e.dmp logfile=e.log
########################################################################################
User-Managed physical backup: one-stop two-copy three-boot
1. Cold standby (datafile offline Backup)
Backup of the database in the shutdown state

2. Hot Standby (datafile online Backup)
Requires archiving mode support

Cold backup:

Mkdir-p/home/oracle/coldbk/

Vi/home/oracle/coldbk/shut.txtconn/as Sysdbashutdown Immediateexit

Vi/home/oracle/coldbk/start.txtconn/as Sysdbastartupexit

Select ' Cp-v ' | | name| | '/home/oracle/coldbk/'
From
(select name from V$controlfile
UNION ALL
Select name from V$datafile
UNION ALL
Select member from V$logfile);

vi/home/oracle/coldbk/bk.sh

export oracle_sid=orclsqlplus /nolog @/home/oracle/coldbk/shut.txtcp -v  $ORACLE _home/ dbs/orapw$oracle_sid /home/oracle/coldbk/cp -v  $ORACLE _home/dbs/spfile$oracle_sid.ora / home/oracle/coldbk/cp -v  $ORACLE _home/dbs/init$oracle_sid.ora /home/oracle/coldbk/cp -v  /u01/app/oracle/oradata/orcl/control01.ctl /home/oracle/coldbk/cp -v /u01/app/oracle/fast _recovery_area/orcl/control02.ctl /home/oracle/coldbk/cp -v /u01/app/oracle/oradata/orcl/ system01.dbf /home/oracle/coldbk/cp -v /u01/app/oracle/oradata/orcl/sysaux01.dbf /home/ Oracle/coldbk/cp -v /u01/app/oracle/oradata/orcl/undotbs01.dbf /home/oracle/coldbk/cp -v  /u01/app/oracle/oradata/orcl/users01.dbf /home/oracle/coldbk/cp -v /u01/app/oracle/ oradata/orcl/redo03.log /home/oracle/coldbk/cp -v /u01/app/oracle/oradata/orcl/redo02.log  /home/oracle/coldbk/cp -v /u01/app/oracle/oradata/orcl/redo01.log /home/oracle/coldbk/sqlplus /nolog @/home/oracle/ Coldbk/start.txt

chmod +x/home/oracle/coldbk/bk.sh

Back to continue to talk about hot backup and Rman backup, please take care, write well, have questions please point out, thank you!!!!

This article is from the "Liang blog" blog, make sure to keep this source http://7038006.blog.51cto.com/7028006/1856658

On Oracle logical Backup, data pump backup and cold backup