dmp from oracle recovery_oracle还原dmp文件/oracle快速还原dmp文件 - CSDN
精华内容
参与话题
  • 1. 检查Oracle实例状态 ,sid SQL< select instance_name,host_name,startup_time,status,database_status from v$instance;   2.查看用户和默认表空间的关系。select username, DEFAULT_TABLESPACE from ...

    迁移之前的准备工作

    在需要导出的数据库执行:

    1. 检查Oracle实例状态 ,sid
    SQL< select instance_name,host_name,startup_time,status,database_status from v$instance;

     

    2.查看用户和默认表空间的关系。select username, DEFAULT_TABLESPACE from dba_users;

     

    3.切换到你需要导出的账户,查看表对应的表空间。select table_name,tablespace_name from user_all_tables;

    select tablespace_name,count(tablespace_name) from user_all_tables group by tablespace_name;

     

    4.检查表空间使用情况
    select
    f.tablespace_name,
    a.total,
    f.free,(a.total-f.free)/1024 "usedSIZE(G)"
    ,round((f.free/a.total)*100) "% Free"
    from
    (select tablespace_name, sum(bytes/(1024*1024))total from dba_data_files group by tablespace_name) a,
    (select tablespace_name,round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f
    WHERE a.tablespace_name =f.tablespace_name(+)
    order by "% Free"

    5.创建dump directory目录: create  or replace directory test_dir as '/home/oracle/dump';
    在操作系统上药创建相应的目录。 ho mkdir /home/oracle/dump
    6.授权:Grant read,write on directory test_dir to cesm;
    7.导出数据:
    导出全库:ho expdp \'/ as sysdba\' full=y directory=test_dir dumpfile=full.dmp   
    
     
    导出指定用户:ho expdp \'/ as sysdba\' schemas=bankuser directory=test_dir dumpfile=bank.dmp 
    windows下是:ho expdp '/ as sysdba' schemas=bankuser directory=test_dir dumpfile=bank.dmp 
    导出指定表空间:ho expdp bankuser/bankuser tablespaces=bank  directory=test_dir dumpfile=tablespace.dmp  
    导出指定表: ho expdp bankuser/bankuser tables=emp% directory=test_dir dumpfile=table.dmp  (emp%:以emp开头的表)
    带条件导出单个表:ho expdp scott/cat tables=emp query=\'where deptno=10\' directory=test_dir dumpfile=emp.dmp 
    带条件导出多个表:ho expdp scott/cat tables=emp,emp1,dept query=\'where deptno=10\' directory=test_dir dumpfile=emp3.dmp 
     
    或 expdp \"testuser/testuser\" DIRECTORY=test_dir dumpfile=cu.dmp tables=test query=\"where cust like \'066%\'\" 
    query="where cust_no like '066%'"  注意转义字符
    只导出定义:
    expdp system/<password> DIRECTORY=datamove DUMPFILE=schema_exp.dmp SCHEMAS=scott content=metadata_only
    
    导出表
    Linux/Unix Platform
    expdp system/<password> DIRECTORY=datamove DUMPFILE=export.dmp logfile=export.log schemas=scott INCLUDE=table:\"LIKE \'%EMP%\'\" 
    Windos Platform
    expdp system/<password> DIRECTORY=datamove DUMPFILE=export.dmp logfile=export.log schemas=scott INCLUDE=table:\"LIKE '%EMP%'\" 
    
     
    
     
    
     
    
     
    8.导入数据:
    
     
    2)按用户导入, 这里由david执行导入scott的数据表(切莫在末尾加上分号)
    impdp david/abc123 directory=dirdp dumpfile=expdp.dmp remap_schema=scott:david
    
     
    impdp david/abc123 directory=dirdp dumpfile=expdp.dmp schemas=scott remap_schema=scott:david
    
     
    ho impdp system/oracle schemas=bankuser (remap_schema=bankuser:bankuser) directory=test_dir dumpfile=bank.dmp  如果是导入原用户名都不用创建用户,不是原用户名创建后需要改密码。但都要创建对应表空间。
    按表导入:
    ho impdp scott/cat tables=emp directory=test_dir dumpfile=emp.dmp
    
     
    
     
    
     
    按查询条件导入
    impdp david/abc123@orcl schemas=scott remap_schema=scott:david directory=dir
    dp dumpfile=expdp.dmp query='dept:"WHERE DEPTNO=20"';
    
     
    按表空间导出入
    impdp system/oracle directory=dirdp dumpfile=tablespace.dmp tablespaces=users;
    导入整个数据库
    impdp system/oracle directory=dirdp dumpfile=full.dmp full=y;
    
     
    并行导入:expdp scott/tiger@orcl schemas=scott directory=dirdp dumpfile=expdp_%U.dmp logfile=expdp.log parallel=12 job_name=jobname_parallel
    并行导出
    
     
     
    目标库建表空间,用户。
     

     

     

     

    revoke取消授权

    查看所有目录:select * from dba_directories;
    
     

    --查看目录及权限

    Sql代码   

    SELECT PRIVILEGE, DIRECTORY_NAME, DIRECTORY_PATH  

      FROM USER_TAB_PRIVS T, ALL_DIRECTORIES D  

     WHERE T.TABLE_NAME(+) = D.DIRECTORY_NAME  

     ORDER BY 2, 1;  

     

     

    注:非归档模式不能实现一致性备份。

     

    非归档,没归档日志,基本上没办法恢复,只能恢复到rman冷备那个时间点。

     

    非归档下,RMAN只以冷备不能热备,且需要停机。

     

    如果数据重要,还是打开归档吧。

     

    查看oracle数据库是否归档和修改归档模式:

    Oracle分为非归档模式(NOARCHIVELOG) 和归档模式(ARCHIVELOG)。非归档模式不产生归档日志,虽然节省了硬盘空间,但是备份方案选择很有限,通常只能选择冷备份。还原也只能还原到备份那一时刻的数据,通常也仅在开发时使用(据说在数据仓库中也使用),Oracle安装默认就是非归档模式。在生产环境中我们因该使用归档模式,它会产生归档日志,可以使用多种备份和还原方案,对与Oracle管理员来说应该更改模式是必然的选择。

     

    1.查看数据库现有模式可使用以下语句

    select name,log_mode from v$database;

    也可以用下面的语句
    archive log list;(该方法需要as sysdba)

    2.关闭数据库

    SQL> shutdown immediate;

    3.启动数据库到mount模式

    SQL> startup mount;

    4.修改数据库为归档模式
    SQL> alter database archivelog;

    5,打开数据库库
    SQL> alter database open;
    SQL> SELECT NAME,created,log_mode FROM v$database;

    NAME      CREATED   LOG_MODE
    --------- --------- ------------
    ORCL      04-JUN-12 ARCHIVELOG

    6,查看归档日志的路径和目录
    SQL> show parameter db_recovery_file_dest;

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_recovery_file_dest                string      /data/app/oracle/flash_recovery_area
    db_recovery_file_dest_size           big integer 2G

    修改方法:
    SQL> alter system set db_recovery_file_dest_size=20g;
    SQL> alter system set db_recovery_file_dest='/app/arc';

     

     

     

     

     

    展开全文
  • linux命令及oracle备份

    千次阅读 2011-05-26 11:13:00
    1.linux下查看占用空间 df -h2.gzip 压缩文件 gzip xx.dmp xx.dmp.gz

    1.linux下查看占用空间 df -h

    2.gzip 压缩文件 gzip xx.dmp xx.dmp.gz

    3.解压缩 gzip -d xx.dmp.gz

     

    4.查看tomcat进程

    ps -ef |grep tomcat

     

    5.杀死进程

     kill -9 pid

     

    6.导出数据

    exp nciclccd_e/nciclccd_e@LCC buffer=9999999 file=/opt/oracle/backup/exp/trans/20100709140000.dmp

    exp user/password file=/home/oracle/ccode_20110526_bak.dmp tables=CCODE CONSTRAINTS=N INDEXES=N

     

    7.数据导入

     1.只列出导入文件内容:no
     2.创建忽略错误:yes
     3.导入权限:yes
     4.导入表数据:yes
     5.导入整个导出文件:yes

     

    exp userid=user_name/user_pwd@net_service_name owner=user_name file=exp_user_name.dmp  buffer=67108864 statistics=none grants=n consistent=y

     

     

     

    8.冷备份和热备份

     一、冷备份

    1.停止数据库

    SQL> shutdown immediate
    数据库已经关闭。
    已经卸载数据库。
    ORACLE 例程已经关闭。
    SQL>

    2.拷贝路径下:E:/oracle/product/10.2.0/oradata/orcl的文件到备份,目录下

    3.在oracle数据,控制,log发生损害时,复制到原地,覆盖原文件

    4.重新开启数据库startup

     

    二、热备份

      1.热备份必须在日志归档的模式下,进行。

      2.查看日志模式

     SQL> archive log list;
    数据库日志模式             非存档模式
    自动存档             禁用
    存档终点            USE_DB_RECOVERY_FILE_DEST
    最早的联机日志序列     9
    当前日志序列           11

     

    3.启动归档日志模式

      SQL> alter system set log_archive_start=true scope=spfile;

    系统已更改。

     

    4.关闭数据库

    SQL> shutdown immediate;

     

    5.启动数据库(只启动控制文件不启动数据文件)

    SQL> startup mount;
    ORA-32004: obsolete and/or deprecated parameter(s) specified
    ORACLE 例程已经启动。

    Total System Global Area  293601280 bytes
    Fixed Size                  1248600 bytes
    Variable Size             100663976 bytes
    Database Buffers          184549376 bytes
    Redo Buffers                7139328 bytes
    数据库装载完毕。

     

    6.更改数据库为归档方式

     SQL> alter database archivelog;

    数据库已更改。

     

    7.启动数据文件

     SQL> alter database open;

    数据库已更改。

     

    8.再次查看log模式

     SQL> archive log list;
    数据库日志模式            存档模式
    自动存档             启用
    存档终点            USE_DB_RECOVERY_FILE_DEST
    最早的联机日志序列     9
    下一个存档日志序列   11
    当前日志序列           11
    SQL>

     

    9.

    SQL> alter tablespace tab begin backup;

    表空间已更改。

     

    10.拷贝  E:/oracle/product/10.2.0/oradata/orcl相应的表空间文件(tab)到备份目录下

     

    11.拷贝结束

       SQL> alter tablespace tab end backup;

    表空间已更改。

    12.将当前日志归档

     SQL> alter system archive log current;

    系统已更改。

     

    13.归档(执行两次,因为3个log文件)

      SQL> alter system switch logfile;

    系统已更改。

    SQL> alter system switch logfile;

    系统已更改。

     

    14.关闭数据库

     SQL> shutdown immediate

     

    15.删掉数据文件(包含tab表空间的数据文件),重新打开,报错

     

    SQL> startup
    ORA-32004: obsolete and/or deprecated parameter(s) specified
    ORACLE 例程已经启动。

    Total System Global Area  293601280 bytes
    Fixed Size                  1248600 bytes
    Variable Size             109052584 bytes
    Database Buffers          176160768 bytes
    Redo Buffers                7139328 bytes
    数据库装载完毕。
    ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件
    ORA-01110: 数据文件 6: 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TABS.DBF'

     

    16.drop掉6号错误文件

     SQL> alter database datafile 6 offline drop;

    数据库已更改。

     

    17.加载其它数据

     SQL> alter database open;

    数据库已更改。

    18.拷回备份文件(Itabs.dbf)

     

    19.恢复错误文件

     

     SQL> recover datafile 6;

     

    20.挂载文件

     SQL> alter database datafile 6 online;

    数据库已更改。

     

    三、控制文件缺失

    1. 备份控制文件

    SQL> alter database backup controlfile to trace;

    数据库已更改。

    2.控制文件备份到E:/oracle/product/10.2.0/admin/orcl/udump下,最新的一个

    3.打开文件,提取控制文件脚本

     STARTUP NOMOUNT
    CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
        MAXLOGFILES 16
        MAXLOGMEMBERS 3
        MAXDATAFILES 100
        MAXINSTANCES 8
        MAXLOGHISTORY 292
    LOGFILE
      GROUP 1 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO01.LOG'  SIZE 50M,
      GROUP 2 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG'  SIZE 50M,
      GROUP 3 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO03.LOG'  SIZE 50M
    -- STANDBY LOGFILE
    DATAFILE
      'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/SYSTEM01.DBF',
      'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/UNDOTBS01.DBF',
      'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/SYSAUX01.DBF',
      'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/USERS01.DBF',
      'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/EXAMPLE01.DBF',
      'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TABS.DBF'
    CHARACTER SET ZHS16GBK
    ;
    -- Commands to re-create incarnation table
    -- Below log names MUST be changed to existing filenames on
    -- disk. Any one log file from each branch can be used to
    -- re-create incarnation records.
    -- ALTER DATABASE REGISTER LOGFILE 'E:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2010_07_23/O1_MF_1_1_%U_.ARC';
    -- ALTER DATABASE REGISTER LOGFILE 'E:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2010_07_23/O1_MF_1_1_%U_.ARC';
    -- Recovery is required if any of the datafiles are restored backups,
    -- or if the last shutdown was not normal or immediate.
    RECOVER DATABASE
    -- All logs need archiving and a log switch is needed.
    ALTER SYSTEM ARCHIVE LOG ALL;
    -- Database can now be opened normally.
    ALTER DATABASE OPEN;

    存储为文件trl.txt

     

    3.创建控制文件

    SQL> @c:/trl.txt
    ORA-32004: obsolete and/or deprecated parameter(s) specified
    ORACLE 例程已经启动。

    Total System Global Area  293601280 bytes
    Fixed Size                  1248600 bytes
    Variable Size             113246888 bytes
    Database Buffers          171966464 bytes
    Redo Buffers                7139328 bytes

    控制文件已创建。

    ORA-00283: ??????????
    ORA-00264: ?????

     

    系统已更改。


    数据库已更改。

     

    四、log文件的损坏

    重做日志:SQL> alter dabase open resetlog

    展开全文
  • linux手动创建oracle实例全过程

    万次阅读 2018-06-22 15:03:40
    先理解几个概念 oracle跟mysql和mssql的不同,提出了实例和表空间等的概念 实例:即一个运行的服务,不含任何物理数据和内容 数据库:依托于实例运行,数据库和实例可以使1对1的关系,也可以是一对多的管理,即可以...

    先理解几个概念 oracle跟mysql和mssql的不同,提出了实例和表空间等的概念

    实例:即一个运行的服务,不含任何物理数据和内容

    数据库:依托于实例运行,数据库和实例可以使1对1的关系,也可以是一对多的管理,即可以有不同实例加载数据库,但是一个实例只能加载一个数据库

    一个操作系统可以运行多个数据库实例


    因此,想要创建一个新的数据库,必须先运行一个实例。

    但是oracle有表空间的概念,不同用户可以设置不同表空间的访问权,相当于把数据库划分出多个子模块,供不同用户使用

    所以,在一般的情况下,利用表空间和用户控制方式,即可以实现分开独立的数据表管理


    创建表空间和用户比较简单,利用plsql即可以实现


    创建数据库实例(非图形界面):

    //利用crt进入linux操作系统

    $su - oracle //进入oracle用户模式下

    $echo $ORACLE_HOME  //查看oracle的home目录位置 假如home目录/opt/11g/oracle/product/11.2.0/dbhome_1,那么执行cd $ORACLE_HOME等效于cd /opt/11g/oracle/product/11.2.0/dbhome_1

    $echo $ORACLE_BASE  //查看oracle的base目录

     

    //创建实例配置文件

    $cd $ORACLE_HOME

    $cd dbs

    $cp initorcl.ora  initvcenter.ora

    $vi  initvcenter.ora   

    db_name='vcenter'
    vcenter.__java_pool_size=402653184
    vcenter.__large_pool_size=134217728
    vcenter.__oracle_base='/opt/11g/oracle'
    vcenter.__pga_aggregate_target=11341398016
    vcenter.__sga_target=34024194048
    vcenter.__shared_io_pool_size=0
    vcenter.__shared_pool_size=7985954816
    vcenter.__streams_pool_size=67108864
    *.audit_file_dest='/opt/11g/oracle/admin/vcenter/adump'
    *.audit_trail='db'
    *.compatible='11.2.0.4.0'
    *.control_files = (ora_control3, ora_control4)
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='vcenter'
    *.db_recovery_file_dest='/opt/11g/oracle/fast_recovery_area'
    *.db_recovery_file_dest_size=4385144832
    *.diagnostic_dest='/opt/11g/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=YFZD2XDB)'
    *.local_listener=''
    *.open_cursors=300
    *.pga_aggregate_target=11333009408
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.sga_target=34001125376
    *.undo_tablespace='UNDOTBS1'


    //创建实例相关目录--作用未理解


    $cd $ORACLE_BASE

    $cd admin

    $mkdir vcenter

    $cd vcenter

    $mkdir adump  bdump  cdump  pfile  udump

    //创建vcenter数据文件

    cd /oradata

    mkdir vcenter     

    //启动实例

    $export ORACLE_SID=vcenter

    $sqlplus /nolog

    sql>conn /as sysdba;

    sql>startup nomount;

    sql>select instance_name from v$instance;   //正常启动后执行语句可以看到运行的实例


    //创建密码文件--作用还未理解

    $$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwvcenter password=Aa123456* force=y  //文件生成在$ORACLE_HOME/dbs目录下


    //创建数据库脚本如下,进入到sql界面执行下面语句

    sql>

      CREATE DATABASE vcenter
       USER SYS IDENTIFIED BY sys
       USER SYSTEM IDENTIFIED BY manager
       LOGFILE GROUP 1 ('/oradata/vcenter/redo01.log') SIZE 20M,
               GROUP 2 ('/oradata/vcenter/redo02.log') SIZE 20M,
               GROUP 3 ('/oradata/vcenter/redo03.log') SIZE 20M
       MAXLOGFILES 5
       MAXLOGMEMBERS 5
       MAXLOGHISTORY 1
       MAXDATAFILES 100
       MAXINSTANCES 1
       CHARACTER SET US7ASCII
       NATIONAL CHARACTER SET UTF8
       DATAFILE '/oradata/vcenter/system01.dbf' SIZE 325M REUSE
       EXTENT MANAGEMENT LOCAL
       SYSAUX DATAFILE '/oradata/vcenter/sysaux01.dbf' SIZE 325M REUSE
       DEFAULT TABLESPACE tbs_1 datafile '/oradata/vcenter/tbs_1.dbf' size 50m
       DEFAULT TEMPORARY TABLESPACE temp
          TEMPFILE '/oradata/vcenter/temp01.dbf'
          SIZE 20M REUSE
       UNDO TABLESPACE undotbs1
          DATAFILE '/oradata/vcenter/undotbs1.dbf'
          SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

    //其中UNDO TABLESPACE undotbs1必须与实例配置文件中的名字完全一样

    sql>select status from v$instance;

    //status为open表示成功



    //运行后续脚本,创建数据字典及相关视图

    SQL>conn sys as sysdba

    SQL>@?/rdbms/admin/catalog.sql;

    //此过程可能需要10分钟左右

    SQL>@?/rdbms/admin/catproc.sql;

    /此过程可能需要15分钟左右

    SQL>@?/rdbms/admin/catblock.sql;

    SQL>@?/rdbms/admin/catoctk.sql;

    SQL>@?/rdbms/admin/owminst.plb;

    SQL>conn system/ manager

    SQL>@?/sqlplus/admin/pupbld.sql;

    SQL>@?/sqlplus/admin/help/hlpbld.sql helpus.sql

    //配置listener.ora和tnsnames.ora,这两个文件在$ORACLE_HOME/network/admin目录下

    listener.ora

    (SID_LIST=
          (SID_DESC=
             (GLOBAL_DBNAME = vcenter)         
             (ORACLE_HOME = /opt/11g/oracle/product/11.2.0/dbhome_1)
             (SID_NAME = vcenter)
           )
      )

    tnsnames.ora
    vcenter =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = vcenter)
        )
      )



    很多具体细节还不是很明白,以后弄通了再补充!

    参考资料:

    http://blog.csdn.net/sunchenglu7/article/details/39676659

    http://www.linuxidc.com/Linux/2014-08/105552.htm

    -------------------------------------------------------------------------------------------

    服务器重启后,如何启动oracle

    su - oracle

    $lsnrctl status

    $lsnrctl start  //启动监听

    $export ORACLE_SID=vcenter

    $sqlplus /nolog

    sql>conn /as sysdba;

    sql>startup

    sql>select status from v$instance;


    不过oracle启动模式有3种:

     Startup nomount  (nomount模式)启动实例不加载数据库。

    Startup mount (mount模式)启动实例加载数据库但不打开数据库

     Startup (open 模式)启动实例加载并打开数据库,就是我们上面所用的命令

     Nomount模式中oracle仅为实例创建各种内存结构和服务进程,不会打开任何数据库文件


    数据库的关闭(SHUTDOWN)

    对于数据库的关闭,有四种不同的关闭选项,下面对其进行一一介绍。

    1、SHUTDOWN NORMAL

    这是数据库关闭SHUTDOWN命令的确省选项。也就是说假如您发出SHUTDOWN这样的命令,也即是SHUTDOWN NORNAL的意思。

    发出该命令后,任何新的连接都将再不允许连接到数据库。在数据库关闭之前,Oracle将等待现在连接的任何用户都从数据库中退出后才开始关闭数据库。采用这种方式关闭数据库,在下一次启动时无需进行任何的实例恢复。但需要注意一点的是,采用这种方式,也许关闭一个数据库需要几天时间,也许更长。

    2、SHUTDOWN IMMEDIATE

    这是我们常用的一种关闭数据库的方式,想很快地关闭数据库,但又想让数据库干净的关闭,常采用这种方式。

    当前正在被Oracle处理的SQL语句立即中断,系统中任何没有提交的事务全部回滚。假如系统中存在一个很长的未提交的事务,采用这种方式关闭数据库也需要一段时间(该事务回滚时间)。系统不等待连接到数据库的任何用户退出系统,强行回滚当前任何的活动事务,然后断开任何的连接用户。

    3、SHUTDOWN TRANSACTIONAL

    该选项仅在Oracle 8i后才能够使用。该命令常用来计划关闭数据库,他使当前连接到系统且正在活动的事务执行完毕,运行该命令后,任何新的连接和事务都是不允许的。在任何活动的事务完成后,数据库将和SHUTDOWN IMMEDIATE同样的方式关闭数据库。

    4、SHUTDOWN ABORT

    这是关闭数据库的最后一招,也是在没有任何办法关闭数据库的情况下才不得不采用的方式,一般不要采用。假如下列情况出现时能够考虑采用这种方式关闭数据库。 数据库处于一种非正常工作状态,不能用shutdown normal或shutdown immediate这样的命令关闭数据库;



          所以说:

       1)  创建新数据库

       2)  重建控制文件

         这2种操作都必须在这个模式下进行。

          Mount模式中oracle只装载数据库但不打开数据库,所以说:

         1)     重命名数据文件

         2)     添加、删除和重命名重做日子文件

         3)     执行数据库完全恢复操作

         4)     改变数据库的归档模式

         这4种操作都必须在这个模式下进行

         Open模式(就是我们上面的startup不带任何参数的)正常启动。

        当然这3种模式之间可以转换:

        Alter database mount(nomount模式)—〉alter database open(mount 模式)—〉(open模式)


       当然还有其它一些情况,在我们open模式下可以将数据库设置为非受限状态和受限状态

      在受限状态下,只有DBA才能访问数据库,所以说:

    1)  执行数据导入导出

    2)  使用sql*loader提取外部数据

    3)  需要暂时拒绝普通用户访问数据库

    4)  进行数据库移植或者升级操作


    关闭服务器防火墙

    # serviceiptables stop   关闭防火墙,会话级起效,重启后失效

    # chkconfigiptables off   不随操作系统自动启动


    oracle数据库备份与还原

    表导出与还原

    imp admin/admin@实例名 file=/dbbackup/table_20180109.dmp tables=yfplss03   
    exp admin/admin@实例名 file=/dbbackup/table_20180109.dmp .dmp  log=$DIR/$DATE'/table_log_'$DATE'.log' TABLES=table

    完全导出

    exp yfplss03/yfplss03@YFZD2 file=$DIR/$DATE'/sbkfw_'$DATE'.dmp' log=$DIR/$DATE'/sbkfw_log_'$DATE'.log'

     


    有三种主要的方式(完全、用户、表)
          1、完全:
              EXP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y
              如果要执行完全导出,必须具有特殊的权限
          2、用户模式:
              EXP SONIC/SONIC    BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC
              这样用户SONIC的所有对象被输出到文件中。
          3、表模式:
              EXP SONIC/SONIC    BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(SONIC)
              这样用户SONIC的表SONIC就被导出
        2、IMP:
          具有三种模式(完全、用户、表)
          1、完全:
              IMP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y
          2、用户模式:
              IMP SONIC/SONIC    BUFFER=64000 FILE=C:\SONIC.DMP FROMUSER=SONIC TOUSER=SONIC
              这样用户SONIC的所有对象被导入到文件中。必须指定FROMUSER、TOUSER参数,这样才能导入数据。
          3、表模式:
              EXP SONIC/SONIC    BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(SONIC)
              这样用户SONIC的表SONIC就被导入。


    http://blog.csdn.net/lsyuan1989/article/details/50418665

    https://www.cnblogs.com/yugen/archive/2010/07/25/1784763.html



    --------------------------------补充笔记-----------------------------------------

    select * from v$session where status ='ACTIVE' 

    select * from v$locked_object

    select * from all_objects

    select * from dba_jobs_running

    select * from dba_jobs


    查看Oracle正在执行的任务

    select a.program, b.spid, c.sql_text,c.SQL_ID
    from v$session a, v$process b, v$sqlarea c
    where a.paddr = b.addr
    and a.sql_hash_value = c.hash_value
    and a.username is not null;

    由于sql_text列没有显示完整
    的sql语句.所以找到sql_id:686nqabc8sgs2再查询v$sql
    select a.* from v$sql a where a.SQL_ID='686nqabc8sgs2'
    可以查看完整的sql文本内容
     
     
     --查询Oracle正在执行的sql语句及执行该语句的用户

    SELECT b.sid oracleID,
    b.username 登录Oracle用户名,
    b.serial#,
    spid 操作系统ID,
    paddr,
    sql_text 正在执行的SQL,
    b.machine 计算机名
    FROM v$process a, v$session b, v$sqlarea c
    WHERE a.addr = b.paddr
    AND b.sql_hash_value = c.hash_value

     

    --查看正在执行sql的发起者的发放程序

    SELECT OSUSER 电脑登录身份,
    PROGRAM 发起请求的程序,
    USERNAME 登录系统的用户名,
    SCHEMANAME,
    B.Cpu_Time 花费cpu的时间,
    STATUS,
    B.SQL_TEXT 执行的sql
    FROM V$SESSION A
    LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
    AND A.SQL_HASH_VALUE = B.HASH_VALUE
    ORDER BY b.cpu_time DESC

     

    --查出oracle当前的被锁对象

    SELECT l.session_id sid,
    s.serial#,
    l.locked_mode 锁模式,
    l.oracle_username 登录用户,
    l.os_user_name 登录机器用户名,
    s.machine 机器名,
    s.terminal 终端用户名,
    o.object_name 被锁对象名,
    s.logon_time 登录数据库时间
    FROM v$locked_object l, all_objects o, v$session s
    WHERE l.object_id = o.object_id
    AND l.session_id = s.sid
    ORDER BY sid, s.serial#;

    ------------------------数据库关闭不成功处理办法--------------------------------

    关闭数据库是shutdown 后面没有接关闭参数中的任何一个。

    nomal --->所有连接都断开时才能关闭;

    transactional --->等待事务结束后,主动断开连接;

    immediate --->主动断开事务和连接

    abort --->立刻关闭数据库,这个操作是危险的,不会同步数据,不触发检查点,回滚段直接清 空,相当于掉电,每次启动都要实例恢复。

    所以,数据库关闭很慢,这时我一心急,就直接退出了sqlplus,造成Oracle文件被lock,当我再次startup时,操作失败,因为文件依然被锁定状态。报错ORA-01012: not logged on.

    后来重启服务,就可以用了,看了oracle的报错解释,更让我费解。虽然问题解决了,但是生产环境是不能随便down机的,所以,这个问题待续…

    kill oracle 进程 或者关掉oracle

    ps -ef|grep ora_dbw0_$ORACLE_SID

    kill -9 pid

    重新启动oracle

    sqlplus sys as sysdba

    startup;


    展开全文
  • Transport the tablespace on Same OS platform

    千次阅读 2011-05-01 09:59:00
    Transport the tablespace on Same OS platform You want to transport tablespaces using RMAN backups instead of performing the transportable tablespaces operation on the live production database....

    Transport the tablespace on Same OS platform

    You want to transport tablespaces using RMAN backups instead of performing the transportable tablespaces operation  on the live production database.

    Source Database : MYTEST

    Target Database : MYDB

    Transportable Tablespace : TRANSPORT

    • You can also transport tablespaces from a “live” database using an alternative transport tablespace technique explained in the Oracle manula,The disadvantages in that method is that the transportable tablespces must be put in  a read only mode, thus affecting database availablity. it not only may be time consuming to put the tablespace into read only mode but users cann’t write to those tablespaces for the duration of the tablespace transport. You don’t have any of these limitations when you use RMAN backups as the basis of your transportable tablespace operation.
    • RMAN backups to create transportable tablespaces is that your RMAN backups must be recoverable to the SCN at which you want the transportable tablespaces.
    • RMAN creates an auxiliary database instance through which it creates the transportable tablespace sets. RMAN does quites a few things in order to prepare the transportable table set.

    The following example shows how to transport tablespaces on identical operating system platforms by utilizing RMAN backups.

    Step 1: Make sure the tablespaces you plan to transport are self contained. To be considered self contained, the tablespaces se you want to transport
    muchn’t contain references pointing outside those tablespaces, such as an index on a table that doesn’t belong to one of the tablespaces you’re transporting.

    SQL> execute sys.dbms_tts.transport_set_check(‘transport’,TRUE);

    PL/SQL procedure successfully completed.

    Step 2: Generate the transportable tablespace set by issuing the transport tablespace command.

    RMAN> transport tablespace ‘TRANSPORT’
    2> tablespace destination ‘/home/oracle/oracle/transporttbs’
    3> auxiliary destination ‘/home/oracle/oracle/auxdest’;

    Creating automatic instance, with SID=’gjEd’

    initialization parameters used for automatic instance:
    db_name=MYTEST
    compatible=10.2.0.1.0
    db_block_size=8192
    db_files=200
    db_unique_name=tspitr_MYTEST_gjEd
    large_pool_size=1M
    shared_pool_size=110M
    #No auxiliary parameter file used
    db_create_file_dest=/home/oracle/oracle/auxdest
    control_files=/home/oracle/oracle/auxdest/cntrl_tspitr_MYTEST_gjEd.f

    starting up automatic instance MYTEST

    Oracle instance started

    Total System Global Area     201326592 bytes

    Fixed Size                     1218508 bytes
    Variable Size                146802740 bytes
    Database Buffers              50331648 bytes
    Redo Buffers                   2973696 bytes
    Automatic instance created

    contents of Memory Script:
    {
    # set the until clause
    set until  scn 580515;
    # restore the controlfile
    restore clone controlfile;
    # mount the controlfile
    sql clone ‘alter database mount clone database’;
    # archive current online log for tspitr to a resent until time
    sql ‘alter system archive log current’;
    # avoid unnecessary autobackups for structural changes during TSPITR
    sql ‘begin dbms_backup_restore.AutoBackupFlag(FALSE); end;’;
    # resync catalog after controlfile restore
    resync catalog;
    }
    executing Memory Script

    executing command: SET until clause

    Starting restore at 29-MAY-10
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: sid=37 devtype=DISK

    channel ORA_AUX_DISK_1: starting datafile backupset restore
    channel ORA_AUX_DISK_1: restoring control file
    channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/autobackup/2010_05_29/o1_mf_s_720285190_60216pdz_.bkp
    channel ORA_AUX_DISK_1: restored backup piece 1
    piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/autobackup/2010_05_29/o1_mf_s_720285190_60216pdz_.bkp tag=TAG20100529T151310
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
    output filename=/home/oracle/oracle/auxdest/cntrl_tspitr_MYTEST_gjEd.f
    Finished restore at 29-MAY-10

    sql statement: alter database mount clone database

    sql statement: alter system archive log current

    sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

    starting full resync of recovery catalog
    full resync complete
    released channel: ORA_DISK_1
    released channel: ORA_AUX_DISK_1

    contents of Memory Script:
    {
    # generated tablespace point-in-time recovery script
    # set the until clause
    set until  scn 580515;
    # set an omf destination filename for restore
    set newname for clone datafile  1 to new;
    # set an omf destination filename for restore
    set newname for clone datafile  2 to new;
    # set an omf destination filename for restore
    set newname for clone datafile  3 to new;
    # set an omf destination tempfile
    set newname for clone tempfile  1 to new;
    # set a destination filename for restore
    set newname for datafile  8 to
    “/home/oracle/oracle/transporttbs/transport01.dbf”;
    # rename all tempfiles
    switch clone tempfile all;
    # restore the tablespaces in the recovery set plus the auxilliary tablespaces
    restore clone datafile  1, 2, 3, 8;
    switch clone datafile all;
    #online the datafiles restored or flipped
    sql clone “alter database datafile  1 online”;
    #online the datafiles restored or flipped
    sql clone “alter database datafile  2 online”;
    #online the datafiles restored or flipped
    sql clone “alter database datafile  3 online”;
    #online the datafiles restored or flipped
    sql clone “alter database datafile  8 online”;
    # make the controlfile point at the restored datafiles, then recover them
    recover clone database tablespace  “TRANSPORT”, “SYSTEM”, “UNDOTBS1″, “SYSAUX” delete archivelog;
    alter clone database open resetlogs;
    # PLUG HERE the creation of a temporary tablespace if export fails due to lack
    # of temporary space.
    # For example in Unix these two lines would do that:
    #sql clone “create tablespace aux_tspitr_tmp
    #           datafile ”/tmp/aux_tspitr_tmp.dbf” size 500K”;
    }
    executing Memory Script

    executing command: SET until clause

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    renamed temporary file 1 to /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_temp_%u_.tmp in control file

    Starting restore at 29-MAY-10
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: sid=39 devtype=DISK

    channel ORA_AUX_DISK_1: starting datafile backupset restore
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    restoring datafile 00001 to /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_system_%u_.dbf
    restoring datafile 00002 to /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_undotbs1_%u_.dbf
    restoring datafile 00003 to /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_sysaux_%u_.dbf
    restoring datafile 00008 to /home/oracle/oracle/transporttbs/transport01.dbf
    channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/backupset/2010_05_29/o1_mf_nnndf_TAG20100529T154405_60230pbp_.bkp
    channel ORA_AUX_DISK_1: restored backup piece 1
    piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/backupset/2010_05_29/o1_mf_nnndf_TAG20100529T154405_60230pbp_.bkp tag=TAG20100529T154405
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:38
    Finished restore at 29-MAY-10

    datafile 1 switched to datafile copy
    input datafile copy recid=9 stamp=720287348 filename=/home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_system_602372kr_.dbf
    datafile 2 switched to datafile copy
    input datafile copy recid=10 stamp=720287348 filename=/home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_undotbs1_602372qx_.dbf
    datafile 3 switched to datafile copy
    input datafile copy recid=11 stamp=720287348 filename=/home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_sysaux_602372o0_.dbf
    datafile 8 switched to datafile copy
    input datafile copy recid=12 stamp=720287348 filename=/home/oracle/oracle/transporttbs/transport01.dbf

    sql statement: alter database datafile  1 online

    sql statement: alter database datafile  2 online

    sql statement: alter database datafile  3 online

    sql statement: alter database datafile  8 online

    Starting recover at 29-MAY-10
    using channel ORA_AUX_DISK_1

    starting media recovery

    archive log thread 1 sequence 15 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_29/o1_mf_1_15_6022vddo_.arc
    archive log thread 1 sequence 16 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_29/o1_mf_1_16_60235d3p_.arc
    archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_29/o1_mf_1_15_6022vddo_.arc thread=1 sequence=15
    archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_29/o1_mf_1_16_60235d3p_.arc thread=1 sequence=16
    media recovery complete, elapsed time: 00:00:03
    Finished recover at 29-MAY-10

    database opened

    contents of Memory Script:
    {
    #mark read only the tablespace that will be exported
    sql clone “alter tablespace TRANSPORT read only”;
    # create directory for datapump export
    sql clone “create or replace directory STREAMS_DIROBJ_DPDIR as ”
    /home/oracle/oracle/transporttbs””;
    # export the tablespaces in the recovery set
    host ‘expdp userid=/”/@/(DESCRIPTION=/(ADDRESS=/(PROTOCOL=beq/)/(PROGRAM=/home/oracle/oracle/product/10.2.0/db_1/bin/oracle/)/(ARGV0=oraclegjEd/)/(ARGS=^’/(DESCRIPTION=/(LOCAL=YES/)/(ADDRESS=/(PROTOCOL=beq/)/)/)^’/)/(ENVS=^’ORACLE_SID=gjEd^’/)/)/(CONNECT_DATA=/(SID=gjEd/)/)/) as sysdba/” transport_tablespaces=
    TRANSPORT dumpfile=
    dmpfile.dmp directory=
    STREAMS_DIROBJ_DPDIR logfile=
    explog.log’;
    }
    executing Memory Script

    sql statement: alter tablespace TRANSPORT read only

    sql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ”/home/oracle/oracle/transporttbs”

    Export: Release 10.2.0.1.0 – Production on Saturday, 29 May, 2010 15:49:28

    Copyright (c) 2003, 2005, Oracle.  All rights reserved.

    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
    With the Partitioning, OLAP and Data Mining options
    Starting “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″:  userid=”/********@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/home/oracle/oracle/product/10.2.0/db_1/bin/oracle)(ARGV0=oraclegjEd)(ARGS=/(DESCRIPTION=/(LOCAL=YES/)/(ADDRESS=/(PROTOCOL=beq/)/)/))(ENVS=ORACLE_SID=gjEd))(CONNECT_DATA=(SID=gjEd))) AS SYSDBA” transport_tablespaces= TRANSPORT dumpfile=dmpfile.dmp directory=STREAMS_DIROBJ_DPDIR logfile=explog.log
    Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
    Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
    Master table “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
    /home/oracle/oracle/transporttbs/dmpfile.dmp
    Job “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at 15:49:48

    host command complete
    /*
    The following command may be used to import the tablespaces.
    Substitute values for <logon> and <directory>.
    impdp <logon> directory=<directory> dumpfile= ‘dmpfile.dmp’ transport_datafiles= /home/oracle/oracle/transporttbs/transport01.dbf
    */
    ————————————————————–
    – Start of sample PL/SQL script for importing the tablespaces
    ————————————————————–
    – creating directory objects
    CREATE DIRECTORY STREAMS$DIROBJ$1 AS  ‘/home/oracle/oracle/transporttbs/’;
    CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS  ‘/home/oracle/oracle/transporttbs’;
    /* PL/SQL Script to import the exported tablespaces */
    DECLARE
    – the datafiles
    tbs_files     dbms_streams_tablespace_adm.file_set;
    cvt_files     dbms_streams_tablespace_adm.file_set;
    – the dumpfile to import
    dump_file     dbms_streams_tablespace_adm.file;
    dp_job_name   VARCHAR2(30) := NULL;
    – names of tablespaces that were imported
    ts_names       dbms_streams_tablespace_adm.tablespace_set;
    BEGIN
    – dump file name and location
    dump_file.file_name :=  ‘dmpfile.dmp’;
    dump_file.directory_object := ‘STREAMS$DIROBJ$DPDIR’;
    – forming list of datafiles for import
    tbs_files( 1).file_name :=  ‘transport01.dbf’;
    tbs_files( 1).directory_object :=  ‘STREAMS$DIROBJ$1′;
    – import tablespaces
    dbms_streams_tablespace_adm.attach_tablespaces(
    datapump_job_name      => dp_job_name,
    dump_file              => dump_file,
    tablespace_files       => tbs_files,
    converted_files        => cvt_files,
    tablespace_names       => ts_names);
    – output names of imported tablespaces
    IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
    FOR i IN ts_names.first .. ts_names.last LOOP
    dbms_output.put_line(‘imported tablespace ‘|| ts_names(i));
    END LOOP;
    END IF;
    END;
    /
    – dropping directory objects
    DROP DIRECTORY STREAMS$DIROBJ$1;
    DROP DIRECTORY STREAMS$DIROBJ$DPDIR;
    ————————————————————–
    – End of sample PL/SQL script
    ————————————————————–

    Removing automatic instance
    shutting down automatic instance
    Oracle instance shut down
    Automatic instance removed
    auxiliary instance file /home/oracle/oracle/auxdest/cntrl_tspitr_MYTEST_gjEd.f deleted
    auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_system_602372kr_.dbf deleted
    auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_undotbs1_602372qx_.dbf deleted
    auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_sysaux_602372o0_.dbf deleted
    auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/datafile/o1_mf_temp_6023bh4x_.tmp deleted
    auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/onlinelog/o1_mf_1_6023b963_.log deleted
    auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/onlinelog/o1_mf_2_6023bb7g_.log deleted
    auxiliary instance file /home/oracle/oracle/auxdest/TSPITR_MYTEST_GJED/onlinelog/o1_mf_3_6023bc3y_.log deleted

    Operation :

    • RMAN starts an auxiliary instance in nomount mode, you don’t have to specify a parameter file for this auixliary instance, RMAN automatically creates the file.
    • RMAN restores a backup of the target database control file and uses it to mount the auxiliary database.
    • Using the switch operation, RMAN restores all datafiles from the target database for the auxiliary instance. These files are restored to the location specified by the auxiliary destination clause in the transportable tablespace command.
    • RMAN also stores the files pertaining to the tablepsaces in the transportable tablspace set in the location specified by the tablespace destination parameter in the transport tablespace command.
    • Once the datafile from the target database are all restored to the auxiliary database location, RMAN performs a point in time recovery of the auxiliary instance.
    • Rman invoked the data pump Export utility to create teh export dump file containg the tablespaces in the transportable tablespace set.
    • Export dump file is places in the location specified by the tablespace destination clause of the transport tablespace command.
    • RMAN alsp generates a Data pump import script you can use to plug in the transported tablespaces into target database.
    • RMAN shuts down the auxiliary instance and automatically deletes all the files created and used during the transport tablespace process. The only files that remain are the transportable set files,
      data pump export log and sample Datapump Import the tablespaces.

    Step 4:  Run import script files to target database. The script files generated when you’re done tablespace transport command used by rman , source database.

    RMAN> SQL> select name from v$database;

    NAME
    ———
    MYDB

    SQL> conn system/azar@mydb
    Connected.
    SQL> CREATE DIRECTORY STREAMS$DIROBJ$1 AS  ‘/home/oracle/oracle/transporttbs/’;

    Directory created.

    SQL> CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS  ‘/home/oracle/oracle/transporttbs’;

    Directory created.

    SQL>DECLARE
    – the datafiles
    tbs_files     dbms_streams_tablespace_adm.file_set;
    cvt_files     dbms_streams_tablespace_adm.file_set;
    – the dumpfile to import
    dump_file     dbms_streams_tablespace_adm.file;
    dp_job_name   VARCHAR2(30) := NULL;
    – names of tablespaces that were imported
    ts_names       dbms_streams_tablespace_adm.tablespace_set;
    BEGIN
    – dump file name and location
    dump_file.file_name :=  ‘dmpfile.dmp’;
    dump_file.directory_object := ‘STREAMS$DIROBJ$DPDIR’;
    – forming list of datafiles for import
    tbs_files( 1).file_name :=  ‘transport01.dbf’;
    tbs_files( 1).directory_object :=  ‘STREAMS$DIROBJ$1′;
    – import tablespaces
    dbms_streams_tablespace_adm.attach_tablespaces(
    datapump_job_name      => dp_job_name,
    dump_file              => dump_file,
    tablespace_files       => tbs_files,
    converted_files        => cvt_files,
    tablespace_names       => ts_names);
    – output names of imported tablespaces
    IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
    FOR i IN ts_names.first .. ts_names.last LOOP
    dbms_output.put_line(‘imported tablespace ‘|| ts_names(i));
    END LOOP;
    END IF;
    END;
    31  /

    PL/SQL procedure successfully completed.

    SQL> select tablespace_name from dba_data_files;

    TABLESPACE_NAME
    ——————————
    SYSTEM
    UNDOTBS1
    SYSAUX
    USERS
    TRANSPORT

    SQL>

    Possible Error :-

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of tranport tablespace command at 05/29/2010 15:36:00
    RMAN-06004: ORACLE error from recovery catalog database: RMAN-20202: tablespace not found in the recovery catalog

    If you don’t have backup copy of your transportable tablespace in RMAN backupsets, the above error will be shown.

    展开全文
  • IMP-00003: ORACLE error 3113 encountered ORA-03113: end-of-file on communication channel . . importing table "SFP_FILE_6" 11179 rows imported IMP-00017: following statement failed w
  • ORACLE EXP命令

    万次阅读 2010-06-18 23:56:00
    本文对Oracle数据的导入导出 imp ,exp 两个命令进行了介绍, 并对其相应的参数进行了说明,然后通过一些示例进行演练,加深理解. 文章最后对运用这两个命令可能出现的问题(如权限不够,不同oracle版本)进行了...
  • Oracle数据DOS命令导入导出imp/exp

    千次阅读 2016-07-12 09:04:49
    imp 命令是在dos提示符下执行的。 直接cmd后执行而不是在sql下执行的 数据导出: ... exp system/manager@TESTfile=d:daochu.dmp full=y 2 将数据库中system用户与sys用户的表导出  exp syste
  • 利用闪回恢复truncate表的数据

    千次阅读 2017-01-07 18:19:51
    因为truncate不是DML语句,是DDL语句,不能使用闪回查询的方式恢复表数据,这里介绍一种通过...SQL> create table t as select * from all_objects where rownum; Table created. SQL> select object_id from
  • [oracle@centos5 ~]$ expdp gys directory= dmp_dir dumpfile=stream.dmp schemas=stream Export: Release 10.2.0.4.0 - Production on Wednesday, 16 October, 2013 17:51:37 Copyright (c) 2003, 2007, Orac
  • 今天需要将部分表导入到同一个库下的一个新用户下...'/lvora1/app/oracle/oradata/ORCL/tbl_ts/TBL_TS_NEW .DBF' SIZE 512M AUTOEXTEND ON NEXT 128M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEME
  • 数据泵 EXPDP 导出工具的使用

    万次阅读 2013-07-01 17:33:32
    --=================================--数据泵 EXPDP 导出工具的使用--================================= 对于Oracle 数据库之间的导入导出,可以使用Oracle提供的导入导出工具EXP/IMP来实现。EXP/IMP是Oracle早期...
  • oracle中imp命令详解

    万次阅读 2009-09-01 10:27:00
    oracle中imp命令详解 Oracle的导入实用程序(Import utility)允许从数据库提取数据,并且将数据写入操作系统文件。imp使用的基本格式:imp[username[/password[@service]]],以下例举imp常用用法。 1. 获取帮助 imp ...
  • Oracle中imp命令详解

    万次阅读 2019-09-14 17:21:17
    oracle中imp命令详解 Oracle的导入实用程序(Import utility)允许从数据库提取数据,并且将数据写入操作系统文 件。imp使用的基本格式:imp[username[/password[@service]]],以下例举imp常用用 法。 1. 获取帮助 imp...
  • Oracle11g的三种备份方法

    万次阅读 2019-03-05 15:31:55
    select file#, status, enabled, name from V$datafile;--查看数据文件 select * from v$controlfile;--控制文件 select * from v$logfile;--日志文件 二、三种备份方法 1、导入/导出(import/export) 1)支持...
  • oracle导入导出语句

    千次阅读 2012-03-16 11:40:04
    ORACLE导入导出命令解析 本文对Oracle数据的导入导出 imp ,exp 两个命令进行了介绍, 并对其相应的参数进行了说明,然后通过一些示例进行 演练,加深理解. 文章最后对运用这两个命令可能出现的问题(如权限不够,...
  • oracle 学习日志--ORACLE EXP命令

    万次阅读 2011-07-06 14:49:23
    本文对Oracle数据的导入导出 imp ,exp 两个命令进行了介绍, 并对其相应的参数进行了说明,然后通过一些示例进行演练,加深理解.文章最后对运用这两个命令可能出现的问题(如权限不够,不同oracle版本)进行了探讨,并提出...
  • RMAN实例入门,备份与恢复

    千次阅读 2012-01-18 17:03:03
    本文用step by step的方式介绍一下RMAN的入门使用,通常保存备份目录的目录数据库和目标数据库应该在不同的机器上,这里两个数据库在同一台机器上. ... (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
  • oracle imp/exp命令详解

    万次阅读 2012-05-02 17:21:38
    oracle中imp命令详解 Oracle的导入实用程序(Import utility)允许从数据库提取数据,并且将数据写入操作系统文 件。imp使用的基本格式:imp[username[/password[@service]]],以下例举imp常用用 法。 1. 获取...
1 2 3 4 5 ... 20
收藏数 2,410
精华内容 964
关键字:

dmp from oracle recovery