Oracle RMAN备份（逻辑备份）操作

创建逻辑备份数据存放目录
[root@localhost ~]# mkdir -p /rman/dmp
[root@localhost ~]# chmod 777 /rman/dmp/

第一种方法

进入sqlplus环境启动数据库

[oracle@localhost ~]$ sqlplus / as sysdba
SQL> startup
SQL> create directory dmp_dir as '/rman/dmp';
SQL> select * from dba_directories;
SQL> grant write,read on directory dmp_dir to xixi;
[oracle@localhost ~]$ lsnrctl start

1.按用户导出数据

这里可能无法远程连接，多试几次监听lsnrctl status，一定打开数据库

[oracle@localhost ~]$ expdp xixi/wcx1998@127.0.0.1:1521/nbcb914 schemas=xixi directory=dmp_dir dumpfile=20200910.dmp logfile=20200910dmp.log;

Starting "XIXI"."SYS_EXPORT_SCHEMA_01":  xixi/********@127.0.0.1:1521/nbcb914 schemas=xixi directory=dmp_dir dumpfile=20200910.dmp logfile=20200910dmp.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "XIXI"."TEST"                                   0 KB       0 rows
Master table "XIXI"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XIXI.SYS_EXPORT_SCHEMA_01 is:
  /rman/dmp/20200910.dmp
Job "XIXI"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:01:34

由上面信息可以得知，/rman/dmp/生成两个文件，即
20200910.dmp和20200910dmp.log。

2、按表名导

[oracle@localhost ~]$ expdp test_demo/test_demo@orcl TABLES=sys_log,dept dumpfile=sys_log.dmp DIRECTORY=dmp_dir;

按指定一类的表名进行导出，比如导出表名前缀为test_的所有表：

[oracle@localhost ~]$ expdp test_demo/test_demo@orcl TABLES=test_% dumpfile=sys_log.dmp DIRECTORY=dmp_dir;

3、按查询条件导

[oracle@localhost ~]$ expdp test_demo/test_demo@orcl directory=dmp_dir dumpfile=sys_log1.dmp Tables=sys_log query=\"WHERE id=\'5280e\'\";

按用户名备份完成后，到数据备份目录查看

[oracle@localhost ~]$ cd /rman/dmp/
[oracle@localhost dmp]$ ls
20200910.dmp  20200910dmp.log

第二种

[oracle@localhost a]$ sqlplus / as sysdba
SQL> create or replace directory store_position as '/rman/dmp';
SQL> grant write,read on directory store_position to xixi;

这里因为逻辑备份总显示权限问题，最后给了xixi dba权限。

SQL> grant dba to xixi;
全库备份
[oracle@localhost a]$ expdp xixi/wcx1998 directory=store_position dumpfile=full.dmp full=y;

//这时逻辑备份才成功了

[oracle@localhost a]$ cd /rman/dmp/
[oracle@localhost dmp]$ ls
export.log  full.dmp
[oracle@localhost dmp]$ du -sh
98M