-- 查询平台和字节序
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
-- 查询字符集
select userenv('language') from dual;
select 'ALTER TABLESPACE '||name||' READ ONLY;' exec_sql
from v$tablespace
where NAME not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP');
select tablespace_name,status from dba_tablespaces;
执行过程：
SYS@LHR11G> create table lhr.test tablespace ts_lhr as select * from dba_tables;
Table created.
SYS@LHR11G> select count(*) from lhr.test;
COUNT(*)
----------
2835
SYS@LHR11G> select 'ALTER TABLESPACE '||name||' READ ONLY;' exec_sql
2 from v$tablespace
3 where NAME not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP');
EXEC_SQL
--------------------------------------------------------------------------------------------------------------------
ALTER TABLESPACE USERS READ ONLY;
ALTER TABLESPACE EXAMPLE READ ONLY;
ALTER TABLESPACE TS_LHR READ ONLY;
SYS@LHR11G> ALTER TABLESPACE USERS READ ONLY;
Tablespace altered.
SYS@LHR11G> ALTER TABLESPACE EXAMPLE READ ONLY;
Tablespace altered.
SYS@LHR11G> ALTER TABLESPACE TS_LHR READ ONLY;
Tablespace altered.
SYS@LHR11G> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS READ ONLY
EXAMPLE READ ONLY
TS_LHR READ ONLY
7 rows selected.
SYS@LHR11G> select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
DIRECTORY_PATH
---------------------------------------
/u01/app/oracle/admin/LHR11G/dpdump/
-- 源库执行
create database link dbl_lhrpdb1
connect to system identified by "lhr"
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.0.15)(PORT = 1521)) )(CONNECT_DATA =(SERVICE_NAME = lhrpdb1)))';
-- 源库执行
create or replace directory TTS_DATAFILE_SOURCE as '/u01/app/oracle/oradata/LHR11G/';
grant all on directory TTS_DATAFILE_SOURCE to public;
-- 目标库执行
create or replace directory TTS_DATAFILE_DES as '/u01/app/oracle/oradata/lhrcdb1/lhrpdb1/';
grant all on directory TTS_DATAFILE_DES to public;
-- 源库执行，拷贝文件到目标库
exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE_SOURCE',source_file_name => 'example01.dbf',destination_directory_object => 'TTS_DATAFILE_DES',destination_file_name => 'example01.dbf',destination_database => 'dbl_lhrpdb1');
exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE_SOURCE',source_file_name => 'ts_lhr01.dbf',destination_directory_object => 'TTS_DATAFILE_DES',destination_file_name => 'ts_lhr01.dbf',destination_database => 'dbl_lhrpdb1');
exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE_SOURCE',source_file_name => 'users01.dbf',destination_directory_object => 'TTS_DATAFILE_DES',destination_file_name => 'users01.dbf',destination_database => 'dbl_lhrpdb1');
3.2.4、将源数据库中的所有用户表空间设置为读写模式
select 'ALTER TABLESPACE '||name||' READ WRITE;' exec_sql
from v$tablespace
where NAME not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP');
select tablespace_name,status from dba_tablespaces;
CONVERT DATAFILE
'/u01/app/tts/example01.dbf',
'/u01/app/tts/ts_lhr01.dbf',
'/u01/app/tts/users01.dbf'
TO PLATFORM="Linux x86 64-bit"
FROM PLATFORM="Linux x86 64-bit"
DB_FILE_NAME_CONVERT='/u01/app/tts/', '/u01/app/oracle/oradata/lhrcdb1/lhrpdb1/'
PARALLELISM=4;
3.3.2、开始导入
-- 创建目录
create or replace directory tts_dump as '/u01/app/tts/';
grant all on directory tts_dump to public;
select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='TTS_DUMP';
-- 导入
impdp system/lhr@localhost/LHRPDB1 dumpfile=expdp_lhr_20210408.dmp directory=tts_dump \
transport_datafiles='/u01/app/oracle/oradata/lhrcdb1/lhrpdb1/example01.dbf', \
'/u01/app/oracle/oradata/lhrcdb1/lhrpdb1/ts_lhr01.dbf', \
'/u01/app/oracle/oradata/lhrcdb1/lhrpdb1/users01.dbf' \
logfile=import_lhr.log
3.3.3、检查状态
sqlplus system/lhr@localhost/LHRPDB1
col TABLESPACE_NAME format a20
col FILE_NAME format a100
select tablespace_name,file_name from dba_data_files;
select tablespace_name,status from dba_tablespaces;
执行过程：
SYSTEM@localhost/LHRPDB1> col TABLESPACE_NAME format a20
SYSTEM@localhost/LHRPDB1> col FILE_NAME format a100
SYSTEM@localhost/LHRPDB1> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- ----------------------------------------------------------------------------------------------------
SYSTEM /u01/app/oracle/oradata/lhrcdb1/lhrpdb1/system01.dbf
SYSAUX /u01/app/oracle/oradata/lhrcdb1/lhrpdb1/sysaux01.dbf
USERS /u01/app/oracle/oradata/lhrcdb1/lhrpdb1/users01.dbf
EXAMPLE /u01/app/oracle/oradata/lhrcdb1/lhrpdb1/example01.dbf
TS_LHR /u01/app/oracle/oradata/lhrcdb1/lhrpdb1/ts_lhr01.dbf
SYSTEM@localhost/LHRPDB1> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ------------------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS_LHR ONLINE
6 rows selected.
SYSTEM@localhost/LHRPDB1> select count(*) from lhr.test;
COUNT(*)
----------
2835
数据库
发布于：2021-06-30 16:02
|
阅读数：530
|
评论：0
QQ好友和群
QQ空间
