ITPub博客

首页 > 数据库 > Oracle > oracle11g导库备份

oracle11g导库备份

Oracle 作者:andrewyancn 时间:2014-02-27 10:28:36 0 删除 编辑

1、  使用pl/sql工具,查看ods_6002/metabase_6002/data_6002的用户创建语句

 

-- Create the user

create user METABASE_6002

  identified by ""

  default tablespace USERS

  temporary tablespace TEMP

  profile DEFAULT

  password expire;

-- Grant/Revoke role privileges

grant connect to METABASE_6002 with admin option;

grant dba to METABASE_6002;

grant exp_full_database to METABASE_6002;

grant imp_full_database to METABASE_6002;

grant resource to METABASE_6002 with admin option;

-- Grant/Revoke system privileges

grant create view to METABASE_6002;

grant debug any procedure to METABASE_6002;

grant debug connect session to METABASE_6002;

grant select any table to METABASE_6002;

grant unlimited tablespace to METABASE_6002 with admin option;

 

-- Create the user

create user DATA_6002

  identified by ""

  default tablespace DATA_DATA

  temporary tablespace TEMP

  profile DEFAULT

  password expire;

-- Grant/Revoke role privileges

grant connect to DATA_6002 with admin option;

grant dba to DATA_6002 with admin option;

grant exp_full_database to DATA_6002;

grant imp_full_database to DATA_6002;

grant resource to DATA_6002;

-- Grant/Revoke system privileges

grant create view to DATA_6002;

grant select any table to DATA_6002;

grant unlimited tablespace to DATA_6002 with admin option;

 

-- Create the user

create user ODS_6002

  identified by ""

  default tablespace ODS_DATA

  temporary tablespace TEMP

  profile DEFAULT

  password expire;

-- Grant/Revoke role privileges

grant connect to ODS_6002 with admin option;

grant dba to ODS_6002 with admin option;

grant exp_full_database to ODS_6002;

grant imp_full_database to ODS_6002;

grant resource to ODS_6002;

-- Grant/Revoke system privileges

grant create view to ODS_6002;

grant select any table to ODS_6002;

grant unlimited tablespace to ODS_6002 with admin option;

 

注意create user XXX identified by “填写密码”

2、  将要覆盖的用户ods_6002/metabase_6002/data_6002做备份

SQL> select instance_name from v$instance;--查询所在数据库实例

ccbi

SQL> select userenv('language') from dual;--查询数据库字符集为UTF8

SIMPLIFIED CHINESE_CHINA.AL32UTF8

 

设置ORACLE_SID和字符集

[oracle@NEWCORE ~]$ export ORACLE_SID=ccbi

[oracle@NEWCORE ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[oracle@NEWCORE ~]$ exp metabase_6002/123456 file=metabase6002_0731.dmp owner=metabase_6002 log=metabase6002_0731.log

[oracle@NEWCORE ~]$ exp ods_6002/ods_6002 file=ods6002_0731.dmp owner=ods_6002 log=ods6002_0731.log

[oracle@NEWCORE ~]$  exp data_6002/data_6002 file=data6002_0731.dmp owner=data_6002  log=data6002_0731.log

3、  备份想要导入的数据库用户ods_6003/metabase_6003/data_6003

为了防止Oracle11g,空表不导出,需要登录导出的用户分别执行如下的SQL

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;

 

SQL> conn metabase_6003/metabase_6003

Connected.

SQL> select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;

把罗列出来的SQL执行复制出来,执行一遍。

 

 

注意:只执行alter table XXX allocate extent;SQL语句

设置ORACLE_SID和字符集

[oracle@NEWCORE ~]$ export ORACLE_SID=ccbi

[oracle@NEWCORE ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[oracle@NEWCORE ~]$exp metabase_6003/metabase_6003 file=metabase6003_0731.dmp owner=metabase_6003 log=metabase6003_0731.log

[oracle@NEWCORE ~]$ exp ods_6003/ods_6003 file=ods6003_0731.dmp owner=ods_6003 log=ods6003_0731.log

[oracle@NEWCORE ~]$  exp data_6003/data_6003 file=data6003_0731.dmp owner=data_6003 log=data6003_0731.log

4、  删除ods_6002/metabase_6002/data_6002用户

确认数据库实例名

SQL> drop user metabase_6002 cascade;

SQL> drop user data_6002 cascade;

SQL> drop user ods_6002 cascade;

5、  创建用户ods_6002/metabase_6002/data_6002用户

         逐一登录创建的用户来验证,会提示密码过期重新设置,这时重新输入一遍原始密码即可。

或者创建用户的时候password  expire改为 account unlock就不需要用户重新登录修改密码。

6、  将数据覆盖到ods_6002/metabase_6002/data_6002用户之中

设置ORACLE_SID和字符集

[oracle@NEWCORE ~]$ export ORACLE_SID=ccbi

[oracle@NEWCORE ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[oracle@NEWCORE ~]$ imp metabase_6002/123456 file=metabase6003_0731.dmp fromuser=metabase_6003 touser=metabase_6002 log=imp_metabase6002.log

[oracle@NEWCORE ~]$ imp data_6002/data_6002 file=data6003_0731.dmp fromuser=data_6003 touser=data_6002 log=imp_data6002.log

[oracle@NEWCORE ~]$ imp ods_6002/ods_6002 file=ods6003_0731.dmp fromuser=ods_6003 touser=ods_6002 log=imp_ods002.log

 

 

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21620627/viewspace-1114905/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-05-11