oracle11g备份及恢复

oracle备份：

ora文件位置

/myweb/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

create user ADMIN01 identified by ‘123456’;

grant connect,resource,create session to ADMIN01;

grant dba to ADMIN01;

commit;

创建linux下的表空间和临时空间。

mkdir /myweb/databases

chown -R oracle:oinstall /myweb/databases

create TABLESPACE K3CLOUD01 datafile ‘/myweb/databases/K3CLOUD01.DBF’ size 512M autoextend on next 256M permanent online;

创建临时表空间：

create TEMPORARY TABLESPACE JHTEMP tempfile ‘/myweb/databases/JHTEMP01.DBF’ size 128M autoextend on maxsize 1G tablespace group group1;

默认保存路径：

/myweb/app/oracle/product/11.2.0/dbhome_1/rdbms/log/cloud3.dmp

expdp ADMIN01/123456 schemas=ADMIN01 directory=pump_dir dumpfile=cloud3.dmp logfile=cloud3.log exclude=STATISTICS,TABLE:‘LIKE ‘TMP%’’;

create directory pump_dir as ‘/myweb/backup’;

grant read,write on directory pump_dir to ADMIN01;

expdp ADMIN01/123456 schemas=ADMIN01 directory=pump_dir dumpfile=cloud3.dmp logfile=cloud3.log exclude=STATISTICS,TABLE:‘LIKE ‘TMP%’’;

impdp ADMIN01/123456 directory=pump_dir dumpfile=cloud3.dmp REMAP_SCHEMA=ADMIN01:ADMIN02 EXCLUDE=USER;

导入后，执行：

在恢复出来的库中执行下列的语句。–增加表类型，用于IN子句等处理

CREATE OR REPLACE TYPE udt_inttable AS TABLE OF number(19);

GO

CREATE OR REPLACE TYPE udt_varchartable AS TABLE OF varchar(450);

GO

CREATE OR REPLACE TYPE udt_nvarchartable AS TABLE OF varchar2(450);

GO

如果想导入的用户已经存在:

1. 导出用户 expdp user1/pass1 directory=dumpdir dumpfile=user1.dmp
2. 导入用户 impdp user2/pass2 directory=dumpdir dumpfile=user1.dmp REMAP_SCHEMA=user1:user2 EXCLUDE=USER

如果想导入的用户不存在:

1. 导出用户 expdp user1/pass1 directory=dumpdir dumpfile=user1.dmp
2. 导入用户 impdp system/passsystem directory=dumpdir dumpfile=user1.dmp REMAP_SCHEMA=user1:user2
3. user2会自动建立，其权限和使用的表空间与user1相同，但此时用user2无法登录，必须修改user2的密码

file scp

scp cloud3.zip root@192.168.1.10:/myweb/backup

覆盖恢复

impdp ADMIN01/123456 directory=pump_dir dumpfile=cloud1.dmp REMAP_SCHEMA=ADMIN01:ADMIN02 EXCLUDE=USER table_exists_action=replace;

删除oracle中用户ADMIN01

用oracle 登录

执行命令：lsnrctl stop

sqlplus / as sysdba;

shutdown immediate;

startup mount;

alter database open;

drop user ADMIN01 cascade;

退出到命令行

执行命令：lsnrctl start;.

备份脚本：

#filename:backup.sh

#!/bin.sh

now=$(date +%Y%m%d)

cd /myweb/backup

#su oracle

su - oracle -c ‘expdp ADMIN01/123456 schemas=ADMIN01 directory=pump_dir dumpfile=cloud$now.1.dmplogfile=cloud${now}.1.log exclude=STATISTICS,TABLE:\‘LIKE ‘TMP%’\’’

tar czvf cloud$now.1.tar.gzcloud${now}.1.*

rm -rf cloud${now}.1.dmp

expdp ADMIN01/123456 schemas=ADMIN01 CLUSTER=N COMPRESSION=ALL directory=pump_dir dumpfile=cloud${now}.1.dmp exclude=STATISTICS,TABLE:‘LIKE ‘TMP%’’

create user ADMIN01 identified by “123456”;

grant connect,resource,create session to ADMIN01;

grant dba to ADMIN01;

impdp ADMIN01/123456 directory=pump_dir dumpfile=cloud20240527.1.dmp EXCLUDE=USER;