ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > readwrite状态下实现可传输表空间

readwrite状态下实现可传输表空间

原创 Linux操作系统 作者：redhouser 时间：2012-07-10 10:18:22 0 删除 编辑

目的：可传输表空间可以实现迅速将数据迁移/分发的目的，但需要将源库表空间设置为readonly状态，这个条件使该方法无法在生产库联机使用。
不过，可传输表空间支持使用备份生成可传输表空间，从而在不需要修改表空间状态的前提下，实现表空间传递。
该方法使用与表空间时间点恢复（TPITR）类似的方法实现，具体说明可以参考《Database Backup and Recovery Advanced User's Guide》
14 Creating Transportable Tablespace Sets from Backup with RMAN

1，更改为READONLY方式
1.1 源库创建表空间，测试表
SQL> conn / as sysdba
SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/orcl/tbs1.dbf' size 10m;

Tablespace created.

SQL> create tablespace tbs2 datafile '/u01/app/oracle/oradata/orcl/tbs2.dbf' size 10m;

Tablespace created.


SQL> conn system/oracle1
Connected.
SQL> create table tt_table1 tablespace tbs1 as select * from scott.dept;

Table created.

SQL> create table tt_table2 tablespace tbs2 as select * from scott.emp;

Table created.

SQL> create index pk_tt_table1 on tt_table1(deptno) tablespace tbs1

Index created.

SQL> alter table tt_table1
  2  add constraint pk_tt_table1
  3  primary key(deptno)  
  4  using index;

Table altered.

SQL> alter table tt_table2
  2  add constraint fk_tt_table2_r_tab1
  3  foreign key(deptno) references tt_table1(deptno);

1.2源库检查自包含性
SQL> conn / as sysdba
SQL> begin
  2  dbms_tts.transport_set_check('TBS1',true,true);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

VIOLATIONS
--------------------------------------------------------------------------------
Constraint FK_TT_TABLE2_R_TAB1 between table SYSTEM.TT_TABLE1 in tablespace TBS1
 and table SYSTEM.TT_TABLE2 in tablespace TBS2

SQL> begin
  2  dbms_tts.transport_set_check('TBS2',true,true);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

VIOLATIONS
--------------------------------------------------------------------------------
Constraint FK_TT_TABLE2_R_TAB1 between table SYSTEM.TT_TABLE1 in tablespace TBS1
 and table SYSTEM.TT_TABLE2 in tablespace TBS2

SQL> begin
  2    dbms_tts.transport_set_check('TBS1,TBS2',true,true);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

1.3 源库修改表空间为read only
SQL> alter tablespace tbs1 read only;

Tablespace altered.

SQL> alter tablespace tbs2 read only;

Tablespace altered.


1.4源库导出
[oracle@rac1 ~]$ exp \'/ as sysdba\' transport_tablespace=y tablespaces=tbs1,tbs2 file=tt_tbs.dmp

Export: Release 10.2.0.3.0 - Production on Mon Jul 9 19:24:10 2012

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TBS1 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                      TT_TABLE1
EXP-00091: Exporting questionable statistics.
For tablespace TBS2 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                      TT_TABLE2
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully with warnings.

1.5 复制数据文件到目标库
[oracle@rac1 ~]$ cp /u01/app/oracle/oradata/orcl/tbs1.dbf /u01/app/oracle/oradata/emrep/tbs1.dbf
[oracle@rac1 ~]$ cp /u01/app/oracle/oradata/orcl/tbs2.dbf /u01/app/oracle/oradata/emrep/tbs2.dbf

1.6 源库更改表空间状态为read write
conn / as sysdba
SQL> alter tablespace tbs1 read write;

Tablespace altered.

SQL> alter tablespace tbs2 read write;

Tablespace altered.


1.7 目标库导入
[oracle@rac1 ~]$ export ORACLE_SID=emrep

[oracle@rac1 ~]$ imp \'/ as sysdba\' transport_tablespace=y datafiles='/u01/app/oracle/oradata/emrep/tbs1.dbf','/u01/app/oracle/oradata/emrep/tbs2.dbf' tablespaces=tbs1,tbs2 file=tt_tbs.dmp

Import: Release 10.2.0.3.0 - Production on Mon Jul 9 19:33:33 2012

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. . importing table                    "TT_TABLE1"
. . importing table                    "TT_TABLE2"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

1.8 目标库验证
[oracle@rac1 ~]$ sqlplus system/oracle1

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jul 9 19:34:01 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> desc tt_table1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> select * from tt_table1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


SQL> select constraint_name,constraint_type from user_constraints where constraint_name='FK_TT_TABLE2_R_TAB1'

CONSTRAINT_NAME                C
------------------------------ -
FK_TT_TABLE2_R_TAB1            R

 

2，使用备份生成可传输表空间
2.1 源库备份
[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jul 9 19:38:10 2012

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

connected to target database: ORCL (DBID=1314428069)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/orcl/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/backup/orcl/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default

RMAN> backup database plus archivelog;

2.2 源库更新（表空间处于read write状态）
SQL> update system.tt_table1 set loc='xxx';

4 rows updated.

SQL> commit;

Commit complete.

SQL> update system.tt_table1 set dname='xxx';

4 rows updated.

2.3 源库准备目录，用于放置生成的数据文件
[oracle@rac1 oracle]$ pwd
/u01/app/oracle
[oracle@rac1 oracle]$ ls -lrt
drwxr-xr-x 2 oracle oinstall  4096 Jul  9 19:47 ttbs
drwxr-xr-x 2 oracle oinstall  4096 Jul  9 19:49 ttbs_auxdest


2.4 使用备份生成传输表空间
[oracle@rac1 ~]$ rman target /
RMAN> transport tablespace tbs1,tbs2
2>  tablespace destination '/u01/app/oracle/ttbs'
3>  auxiliary destination '/u01/app/oracle/ttbs_auxdest'
4> ;

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in time

List of tablespaces expected to have UNDO segments
tablespace SYSTEM
tablespace UNDOTBS1

Creating automatic instance, with SID='vodh'

initialization parameters used for automatic instance:
db_name=ORCL
compatible=10.2.0.3.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_ORCL_vodh
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/u01/app/oracle/ttbs_auxdest
control_files=/u01/app/oracle/ttbs_auxdest/cntrl_tspitr_ORCL_vodh.f


starting up automatic instance ORCL

Oracle instance started

Total System Global Area     205520896 bytes

Fixed Size                     1260888 bytes
Variable Size                146801320 bytes
Database Buffers              50331648 bytes
Redo Buffers                   7127040 bytes
Automatic instance created

contents of Memory Script.:
{
# set the until clause
set until  scn 767171;
# 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;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 09-JUL-12
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 /u01/app/oracle/backup/orcl/c-1314428069-20120709-05
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/orcl/c-1314428069-20120709-05 tag=TAG20120709T192642
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/ttbs_auxdest/cntrl_tspitr_ORCL_vodh.f
Finished restore at 09-JUL-12

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
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 767171;
# 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  7 to
 "/u01/app/oracle/ttbs/tbs1.dbf";
# set a destination filename for restore
set newname for datafile  8 to
 "/u01/app/oracle/ttbs/tbs2.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, 7, 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  7 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  "TBS1", "TBS2", "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

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 09-JUL-12
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 /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00003 to /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_sysaux_%u_.dbf
restoring datafile 00007 to /u01/app/oracle/ttbs/tbs1.dbf
restoring datafile 00008 to /u01/app/oracle/ttbs/tbs2.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl/1tnfm9s3_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/orcl/1tnfm9s3_1_1 tag=TAG20120709T193947
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:11
Finished restore at 09-JUL-12

datafile 1 switched to datafile copy
input datafile copy recid=21 stamp=788212459 filename=/u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_system_7zokh9hc_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=22 stamp=788212459 filename=/u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_undotbs1_7zokh9m3_.dbf
datafile 3 switched to datafile copy
input datafile copy recid=23 stamp=788212459 filename=/u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_sysaux_7zokh9jh_.dbf
datafile 7 switched to datafile copy
input datafile copy recid=24 stamp=788212459 filename=/u01/app/oracle/ttbs/tbs1.dbf
datafile 8 switched to datafile copy
input datafile copy recid=25 stamp=788212459 filename=/u01/app/oracle/ttbs/tbs2.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  7 online

sql statement: alter database datafile  8 online

Starting recover at 09-JUL-12
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 30 is already on disk as file /u01/app/oracle/archive/orcl/1_30_787328709.dbf
archive log thread 1 sequence 31 is already on disk as file /u01/app/oracle/archive/orcl/1_31_787328709.dbf
archive log filename=/u01/app/oracle/archive/orcl/1_30_787328709.dbf thread=1 sequence=30
archive log filename=/u01/app/oracle/archive/orcl/1_31_787328709.dbf thread=1 sequence=31
media recovery complete, elapsed time: 00:00:02
Finished recover at 09-JUL-12

database opened

contents of Memory Script.:
{
#mark read only the tablespace that will be exported
sql clone "alter tablespace TBS1 read only";
#mark read only the tablespace that will be exported
sql clone "alter tablespace TBS2 read only";
# create directory for datapump export
sql clone "create or replace directory STREAMS_DIROBJ_DPDIR as ''
/u01/app/oracle/ttbs''";
# export the tablespaces in the recovery set
host 'expdp userid=\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/product/10.2.0/db_1/bin/oracle\)\(ARGV0=oraclevodh\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=vodh^'\)\)\(CONNECT_DATA=\(SID=vodh\)\)\) as sysdba\" transport_tablespaces=
 TBS1,
 TBS2 dumpfile=
dmpfile.dmp directory=
STREAMS_DIROBJ_DPDIR logfile=
explog.log';
}
executing Memory Script

sql statement: alter tablespace TBS1 read only

sql statement: alter tablespace TBS2 read only

sql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ''/u01/app/oracle/ttbs''


Export: Release 10.2.0.3.0 - Production on Monday, 09 July, 2012 19:54:56

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  userid="/********@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/u01/app/oracle/product/10.2.0/db_1/bin/oracle)(ARGV0=oraclevodh)(ARGS=\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\))(ENVS=ORACLE_SID=vodh))(CONNECT_DATA=(SID=vodh))) AS SYSDBA" transport_tablespaces= TBS1, TBS2 dumpfile=dmpfile.dmp directory=STREAMS_DIROBJ_DPDIR logfile=explog.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
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:
  /u01/app/oracle/ttbs/dmpfile.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 19:55:25

host command complete
/*
   The following command may be used to import the tablespaces.
   Substitute values for and .
   impdp directory= dumpfile= 'dmpfile.dmp' transport_datafiles= /u01/app/oracle/ttbs/tbs1.dbf, /u01/app/oracle/ttbs/tbs2.dbf
*/
--------------------------------------------------------------
-- Start of sample PL/SQL script. for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS  '/u01/app/oracle/ttbs/';
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS  '/u01/app/oracle/ttbs';
/* 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 :=  'tbs1.dbf';
  tbs_files( 1).directory_object :=  'STREAMS$DIROBJ$1';
  tbs_files( 2).file_name :=  'tbs2.dbf';
  tbs_files( 2).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 /u01/app/oracle/ttbs_auxdest/cntrl_tspitr_ORCL_vodh.f deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_system_7zokh9hc_.dbf deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_undotbs1_7zokh9m3_.dbf deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_sysaux_7zokh9jh_.dbf deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_temp_7zokn7pq_.tmp deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/onlinelog/o1_mf_1_7zokmjx5_.log deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/onlinelog/o1_mf_2_7zokmr11_.log deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/onlinelog/o1_mf_3_7zokmzcm_.log deleted

 

2.5 验证生成的文件
[oracle@rac1 oracle]$ ps -ef|grep pmon
oracle    5539     1  0 18:44 ?        00:00:00 ora_pmon_orcl
oracle    8179     1  0 19:29 ?        00:00:00 ora_pmon_emrep
oracle   12635     1  0 19:51 ?        00:00:00 ora_pmon_vodh
oracle   12686  5959  1 19:52 pts/2    00:00:00 grep pmon
--在rman生成文件过程中可以看到辅助实例的pmon进程：ora_pmon_vodh

[oracle@rac1 oracle]$ ls -lrt ttbs
total 20644
-rw-r----- 1 oracle oinstall 10493952 Jul  9 19:54 tbs2.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul  9 19:54 tbs1.dbf
-rw-r--r-- 1 oracle oinstall     1426 Jul  9 19:55 explog.log
-rw-r----- 1 oracle oinstall   106496 Jul  9 19:55 dmpfile.dmp
-rw-r--r-- 1 oracle oinstall     2191 Jul  9 19:55 impscrpt.sql

[oracle@rac1 oracle]$ ls -lrt ttbs_auxdest
total 7008
drwxr-x--- 4 oracle oinstall    4096 Jul  9 19:52 TSPITR_ORCL_VODH
-rw-r----- 1 oracle oinstall 7159808 Jul  9 19:55 cntrl_tspitr_ORCL_vodh.f
[oracle@rac1 oracle]$ ls -lrt ./ttbs_auxdest/TSPITR_ORCL_VODH
total 8
drwxr-x--- 2 oracle oinstall 4096 Jul  9 19:55 onlinelog
drwxr-x--- 2 oracle oinstall 4096 Jul  9 19:55 datafile
[oracle@rac1 oracle]$ ls -lrt ./ttbs_auxdest/TSPITR_ORCL_VODH/datafile
total 0
[oracle@rac1 oracle]$ ls -lrt ./ttbs_auxdest/TSPITR_ORCL_VODH/onlinelog
total 0


[oracle@rac1 oracle]$ cd ttbs
[oracle@rac1 ttbs]$ more *.sql
/*
   The following command may be used to import the tablespaces.
   Substitute values for and .
   impdp directory= dumpfile= 'dmpfile.dmp' transport_datafil
es= /u01/app/oracle/ttbs/tbs1.dbf, /u01/app/oracle/ttbs/tbs2.dbf
*/
--------------------------------------------------------------
-- Start of sample PL/SQL script. for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS  '/u01/app/oracle/ttbs/';
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS  '/u01/app/oracle/ttbs';
/* 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 :=  'tbs1.dbf';
  tbs_files( 1).directory_object :=  'STREAMS$DIROBJ$1';
  tbs_files( 2).file_name :=  'tbs2.dbf';
  tbs_files( 2).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
--------------------------------------------------------------

2.6 目标库导入
SQL> conn / as sysdba
SQL> create directory ttbsdir as '/u01/app/oracle/ttbs';

Directory created.

[oracle@rac1 ~]$ impdp \'/ as sysdba\' directory=ttbsdir dumpfile='dmpfile.dmp' transport_datafiles= /u01/app/oracle/ttbs/tbs1.dbf, /u01/app/oracle/ttbs/tbs2.dbf

Import: Release 10.2.0.3.0 - Production on Monday, 09 July, 2012 20:19:18

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  '/******** AS SYSDBA' directory=ttbsdir dumpfile=dmpfile.dmp transport_datafiles= /u01/app/oracle/ttbs/tbs1.dbf, /u01/app/oracle/ttbs/tbs2.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 20:19:23


2.7目标库验证导入的数据
SQL> select * from system.tt_table1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

 

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

0
0
上一篇： 使用外部表保存查询结果
下一篇： BOM
请登录后发表评论 登录
全部评论
redhouser

注册时间：2011-05-26

  • 博文量
    211
  • 访问量
    876699

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593