Managing Space for the Database
数据库存储
数据库包括物理结构和逻辑结构。由于物理结构和逻辑结构是分开的，因此管理数据的物理存储时不会影响对逻辑存储结构的访问。
磁盘是数据库的主要存储介质，Oracle DB 可以通过特定于平台的设备驱动程序访问硬盘。（数据库写进程 [和 ASM 进程] 可直接向磁盘进行写入，无需通过操作系统。）
Oracle Database 11g 发行版 2 检测磁盘扇区大小并使用高容量磁盘且不会导致性能下降（原因之一是内部优化减少了可能会发生的重做空间浪费，具有许多短时事务处理的应用程序，如邮件系统，可能会发生重做空间浪费）。
建库脚本
先通过如下脚本找trace文件：
SQL> SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc'
trace_file
FROM (SELECT VALUE
FROM v$parameter
WHERE name = 'user_dump_dest') a,
(SELECT SUBSTR (VALUE, -6, 1) symbol
FROM v$parameter
WHERE name = 'user_dump_dest') b,
(SELECT instance_name FROM v$instance) c,
(SELECT spid
FROM v$session s, v$process p, v$mystat m
WHERE s.paddr = p.addr AND s.sid = m.sid AND m.statistic# = 0) d
TRACE_FILE
------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18168.trc
将控制文件备份到trace：
SQL> alter database backup controlfile to trace;
Database altered.
找到建库脚本：
vi u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18168.trc
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'+DATA/orcl/onlinelog/group_1.261.813297219',
'+FRA/orcl/onlinelog/group_1.257.813297225'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
'+DATA/orcl/onlinelog/group_2.262.813297227',
'+FRA/orcl/onlinelog/group_2.258.813297235'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
'+DATA/orcl/onlinelog/group_3.263.813297239',
'+FRA/orcl/onlinelog/group_3.259.813297245'
) SIZE 50M BLOCKSIZE 512
指定磁盘扇区大小：
在自动存储管理(ASM) 环境下，你可以为磁盘组设置SECTOR_SIZE属性。该属性只能在创建磁盘组时设置（通过使用CREATE DISKGROUP命令）。
你可以选择在CREATE DATABASE命令中使用BLOCKSIZE子句，如上语句中所示。如果未指定块大小，Oracle DB 将检查底层磁盘扇区大小，并在创建重做日志时使用该磁盘扇区大小作为块大小。因此，默认情况下，重做日志块大小是磁盘扇区大小，而不是之前的512 字节扇区大小。
如果使用的硬件支持4k的扇区，可以指定BLOCKSIZE：
CREATE DATABASE sample NORESETLOGS FORCE LOGGING ARCHIVELOG LOGFILE GROUP 1 '$ORACLE_BASE/oradata/sample/redo01.log'
SIZE 100M BLOCKSIZE 4096,
GROUP 2 '$ORACLE_BASE/oradata/sample/redo02.log‘
SIZE 100M BLOCKSIZE 4096
DATAFILE
……
在非4KB扇区磁盘创建4KB块大小日志文件报错：
SQL> alter database add logfile group 4 size 51200K blocksize 4096;
alter database add logfile group 4 size 51200K blocksize 4096
*
ERROR at line 1:
ORA-01378: The logical block size (4096) of file +DATA is not compatible with the disk sector size (media sector size is 512 and host sector size is 512)
传输表空间
直接把数据文件拷贝到目标地
例：
在orcl实例有一个表空间tools
$ export ORACLE_SID=orcl
$ sqlplus / as sysdba
SQL> create tablespace tools datafile '/u01/app/oracle/tools.dbf' size 10M;
SQL> create table scott.test tablespace tools as select * from scott.emp ;
SQL> select count(*) from scott.test ;
将表空间tools迁移到实例ocp上去
方法1：用数据泵导出表空间，再导入
在orcl服务器建立目录对象：
mkdir -p u01/app/oracle/orcl
export ORACLE_SID=orcl
sqlplus / as sysdba
SQL> create directory d_orcl as '/u01/app/oracle/orcl';
grant all on directory d_orcl to public;
$ expdp system/oracle DUMPFILE=tools.dmp DIRECTORY=d_orcl TABLESPACES=tools
在ocp上:
mkdir -p u01/app/oracle/ocp
export ORACLE_SID=ocp
sqlplus / as sysdba
create directory d_ocp as '/u01/app/oracle/ocp';
grant all on directory d_ocp to public;
从orcl上拷贝tools.dmp文件到/u01/app/oracle/ocp
cp u01/app/oracle/orcl/tools.dmp /u01/app/oracle/ocp
SQL> create tablespace tools datafile '+data' size 10M ;
$ impdp system/oracle DUMPFILE=tools.dmp DIRECTORY=d_ocp TABLESPACES=tools
测试:
select count(*) from scott.test ;
方法2：Transporting Tablespaces
直接复制数据文件+导出表空间元数据
主流平台都支持跨平台的表空间传送
COLUMN PLATFORM_NAME FORMAT A36
SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------- --------------
6 AIX-Based Systems (64-bit) Big
16 Apple Mac OS Big
21 Apple Mac OS (x86-64) Little
19 HP IA Open VMS Little
15 HP Open VMS Little
5 HP Tru64 UNIX Little
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
18 IBM Power Based Linux Big
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
13 Linux x86 64-bit Little
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
20 Solaris Operating System (x86-64) Little
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
1. orcl实例中表空间tools
export ORACLE_SID=orcl
sqlplus as sysdba
create tablespace tools datafile '/u01/app/oracle/tools.dbf' size 100M;
create table scott.tool tablespace tools as select * from scott.emp;
2. 把orcl下的表空间tools传送到ocp实例（两个库字符集要一样）
select * from database_properties;
在源和目标上都要执行，是否相同的字节：
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
---------------- --------------
Linux x86 64-bit Little
如果源和目标的平台字节一样，不用对数据文件做转换；不一样则需要做转换。
以下是需要转换的例子（源Linux -- 目标AIX-Based Systems (64-bit)）：
$ rman target
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 21 10:35:25 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1341077951)
RMAN> CONVERT TABLESPACE tools TO PLATFORM 'AIX-Based Systems (64-bit)' FORMAT '/tmp/%U';
Starting conversion at source at 21-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00006 name=/u01/app/oracle/tools.dbf
converted datafile=/tmp/data_D-ORCL_I-1341077951_TS-TOOLS_FNO-6_03o7kj40
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at source at 21-SEP-17
3. Pick a Self-Contained Set of Tablespaces（自我包含性检查）
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TOOLS', TRUE);
查看结果：
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
如：
SQL> create table scott.pdba (id number, time date)
partition by range (time)
(
partition p1 values less than (to_date('2017-8-1', 'yyyy-mm-dd')) tablespace tools ,
partition p2 values less than (to_date('2017-9-1', 'yyyy-mm-dd')) tablespace example ,
partition p3 values less than (to_date('2017-10-1', 'yyyy-mm-dd')) tablespace users ,
partition p4 values less than (maxvalue) tablespace users
);
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TOOLS', TRUE);
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
----------
ORA-39921: Default Partition (Table) Tablespace USERS for PDBA not contained in
transportable set.
ORA-39901: Partitioned table SCOTT.PDBA is partially contained in the transporta
ble set.
如果不自我包含 ：移动表空间
SQL> alter table scott.pdba move partition p1 tablespace users;
再检查：
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TOOLS', TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
没有结果， tools自我包含， 可以传送。
4. 产生要传送的文件
a.先离线要传送的表空间
ALTER TABLESPACE tools READ ONLY;
b.使用data pump 把元数据导出去
先定义目录对象
mkdir -p u01/app/oracle/a
create directory d1 as '/u01/app/oracle/a';
grant all on directory d1 to public;
expdp system/oracle dumpfile=tools.dmp directory=d1 transport_tablespaces=tools logfile=tts_export.log
5. 把数据文件和元数据的dump文件一起拷贝到目标服务器
如果平台一致，不做转换，直接拷贝：
/u01/app/oracle/tools.dbf + tools.dmp
如果平台不一致，转换，拷贝：
converted datafile=/tmp/data_D-ORCL_I-1341077951_TS-TOOLS_FNO-6_03o7kj40 + tools.dmp
6. 在源数据库orcl把tools表空间设置为读写
$ export ORACLE_SID=orcl
$ sqlplus / as sysdba
SQL> ALTER TABLESPACE tools READ write;
7. 在目标数据库ocp加载数据
$ export ORACLE_SID=ocp
$ sqlplus / as sysdba
show parameter instance;
instance_name string ocp
mkdir u01/app/oracle/ocp
cp u01/app/oracle/a/tools.dmp u01/app/oracle/ocp
cp /u01/app/oracle/tools.dbf /u01/app/oracle/ocp
创建目录对象
create directory d2 as '/u01/app/oracle/ocp';
grant all on directory d2 to public;
impdp system/oracle dumpfile=tools.dmp directory=d2 transport_datafiles=/u01/app/oracle/ocp/tools.dbf
8. 在目标库ocp上把表空间设置为读写
SQL> ALTER TABLESPACE tools READ write;
Tablespace altered.
SQL> select count(*) from scott.tool
COUNT(*)
----------
14
直接拷贝了数据文件，速度快。
