pdb正常运行丢失数据文件相关恢复

上篇文章(某个pdb可以在root pdb open状态下进行恢复)发布后,还是有不少朋友有各种争议,比如我的cdb本来就是open的,比如与oracle 12c 新特性文档描述不符等等,这里根据weibo上的各种争论和各位想的测试情况,我继续测试如下
查询数据库相关状态

C:\Users\XIFENFEI>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on 星期二 8月 13 12:49:47 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
SQL> select name from v$datafile where con_id=3;
NAME
--------------------------------------------------------------------------------
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF

删除pdb system01.dbf文件

SQL> host dir E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
 驱动器 E 中的卷没有标签。
 卷的序列号是 000C-3B41
 E:\APP\XIFENFEI\ORADATA\CDB\PDB 的目录
找不到文件

做checkpoint操作

--删除pdb的数据文件(本测试中是system01.dbf),因为dbwr无法写入数据到文件，导致crash，在实际中也有可能是ckpt,lgwr进程等
SQL> alter system checkpoint ;
alter system checkpoint
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结尾
进程 ID: 9316
会话 ID: 136 序列号: 3165

alert日志中写明是dbwr进程无法写文件导致整个cdb crash

Tue Aug 13 12:51:10 2013
Thread 1 advanced to log sequence 316 (LGWR switch)
  Current log# 4 seq# 316 mem# 0: E:\APP\XIFENFEI\ORADATA\CDB\REDO04.LOG
Tue Aug 13 12:51:12 2013
Archived Log entry 5 added for thread 1 sequence 315 ID 0x7377d8de dest 1:
Tue Aug 13 12:51:16 2013
KCF: read, write or open error, block=0x6ad1 online=1
        file=7 'E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF'
        error=27070 txt: 'OSD-04016: 异步 I/O 请求排队时出错。'
Tue Aug 13 12:51:16 2013
Errors in file E:\APP\XIFENFEI\diag\rdbms\cdb\cdb\trace\cdb_dbw0_8612.trc:
Tue Aug 13 12:51:16 2013
Errors in file E:\APP\XIFENFEI\diag\rdbms\cdb\cdb\trace\cdb_dbw0_8612.trc:
ORA-63999: 数据文件出现介质故障
ORA-01114: 将块写入文件 7 时出现 IO 错误 (块 # 27345)
ORA-01110: 数据文件 7: 'E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF'
ORA-27070: 异步读取/写入失败
OSD-04016: 异步 I/O 请求排队时出错。
USER (ospid: 8612): terminating the instance due to error 63999
Tue Aug 13 12:51:17 2013
System state dump requested by (instance=1, osid=8612 (DBW0)), summary=[abnormal instance termination].
System State dumped to trace file E:\APP\XIFENFEI\diag\rdbms\cdb\cdb\trace\cdb_diag_9720.trc
Dumping diagnostic data in directory=[cdmp_20130813125117], requested by (instance=1, osid=8612 (DBW0)), summary=[abnormal instance termination].
Tue Aug 13 12:51:29 2013
Instance terminated by USER, pid = 8612

open cdb数据库

SQL> conn / as sysdba
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area  521936896 bytes
Fixed Size                  2404552 bytes
Variable Size             293605176 bytes
Database Buffers          218103808 bytes
Redo Buffers                7823360 bytes
数据库装载完毕。
ORA-01157: 无法标识/锁定数据文件 7 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 7: 'E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF'
--直接在cdb中无法offline pdb数据文件
SQL> alter database datafile 7 offline;
alter database datafile 7 offline
*
第 1 行出现错误:
ORA-01516: 不存在的日志文件, 数据文件或临时文件 "7"
SQL> alter session set container=pdb;
会话已更改。
SQL> alter database datafile 7 offline;
数据库已更改。
SQL> conn / as sysdba
已连接。
SQL> alter database open;
数据库已更改。
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 断开

恢复并open pdb

C:\Users\XIFENFEI>rman target /
恢复管理器: Release 12.1.0.1.0 - Production on 星期二 8月 13 12:56:54 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
已连接到目标数据库: CDB (DBID=1937199326)
RMAN> restore datafile 7;
启动 restore 于 13-8月 -13
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=361 设备类型=DISK
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00007 还原到 E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
通道 ORA_DISK_1: 正在读取备份片段 D:\PDB_SYS_01OH54LF_1_1.RMAN
通道 ORA_DISK_1: 段句柄 = D:\PDB_SYS_01OH54LF_1_1.RMAN 标记 = TAG20130812T223943
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:16
完成 restore 于 13-8月 -13
RMAN> recover datafile 7;
启动 recover 于 13-8月 -13
使用通道 ORA_DISK_1
正在开始介质的恢复
线程 1 序列 314 的归档日志已作为文件 E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2013_08_12\O1_MF_1_314_90KXOZSF_.
ARC 存在于磁盘上
线程 1 序列 315 的归档日志已作为文件 E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2013_08_13\O1_MF_1_315_90MGSZ0X_.
ARC 存在于磁盘上
线程 1 序列 316 的归档日志已作为文件 E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2013_08_13\O1_MF_1_316_90MH1FGD_.
ARC 存在于磁盘上
归档日志文件名=E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2013_08_12\O1_MF_1_314_90KXOZSF_.ARC 线程=1 序列=314
介质恢复完成, 用时: 00:00:02
完成 recover 于 13-8月 -13
RMAN> exit
恢复管理器完成。
C:\Users\XIFENFEI>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on 星期二 8月 13 12:58:42 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alter session set container=pdb;
会话已更改。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01147: SYSTEM 表空间文件 7 处于脱机状态
SQL> alter database datafile 7 online;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> conn / as sysdba
已连接。
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO

整个操作过程说明一下问题
1.当pdb丢失数据文件或者影响dbwr,lgwr,ckpt等后台进程工作之时,将导致整个cdb crash,因为这些进程都是公用的
2.如果pdb丢失数据文件,通过在pdb中offline该文件,可以正常open cdb和其他pdb,不会长时间影响其他pdb工作
3.在cdb open的情况下,可以恢复pdb的任何数据文件(特殊pdb除外,root,seed之类)

某个pdb可以在root pdb open状态下进行恢复

最近在weibo上有流言:pdb的system还原的时候必须整个cdb处于mount状态下进行,我怎么也想不通ORACLE会这么蠢,如果这样,那如果一个pdb异常了,其他pdb都不能工作对业务影响太大了,经过测试证明事实是:某个pdb可以直接在root pdb open的状态下进行,不影响其他pdb

pdb的system文件备份

E:\dul10>rman target sys/xifenfei@pdb
恢复管理器: Release 12.1.0.1.0 - Production on 星期一 8月 12 22:38:58 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
已连接到目标数据库: CDB (DBID=1937199326)
RMAN> backup tablespace system format 'd:/pdb_sys_%U.rman';
启动 backup 于 12-8月 -13
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=367 设备类型=DISK
通道 ORA_DISK_1: 正在启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集内的数据文件
输入数据文件: 文件号=00007 名称=E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
通道 ORA_DISK_1: 正在启动段 1 于 12-8月 -13
通道 ORA_DISK_1: 已完成段 1 于 12-8月 -13
段句柄=D:\PDB_SYS_01OH54LF_1_1.RMAN 标记=TAG20130812T223943 注释=NONE
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:15
完成 backup 于 12-8月 -13
启动 Control File and SPFILE Autobackup 于 12-8月 -13
段 handle=E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\AUTOBACKUP\2013_08_12\O1_MF_S_823300799_90KWY0OR_.BKP comment=NONE
完成 Control File and SPFILE Autobackup 于 12-8月 -13

使用系统命令强制删除pdb的system文件,使得open pdb之时提示文件不存在,然后使用rman进行还原恢复操作

还原恢复pdb system文件

E:\dul10>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production onXQ 星期一 8月 12 22:43:27 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Product
With the Partitioning, OLAP, Advanced Analytics and Real Application Test
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
SQL> alter session set container=pdb;
会话已更改。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01157: 无法标识/锁定数据文件 7 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 7: 'E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF'
SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 断开
E:\dul10>rman target sys/xifenfei@pdb
恢复管理器: Release 12.1.0.1.0 - Production on 星期一 8月 12 22:44:39 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
已连接到目标数据库: CDB (DBID=1937199326, 未打开)
RMAN> restore datafile 7;
启动 restore 于 12-8月 -13
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=369 设备类型=DISK
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00007 还原到 E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
通道 ORA_DISK_1: 正在读取备份片段 D:\PDB_SYS_01OH54LF_1_1.RMAN
通道 ORA_DISK_1: 段句柄 = D:\PDB_SYS_01OH54LF_1_1.RMAN 标记 = TAG20130812T223943
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:35
完成 restore 于 12-8月 -13
RMAN> recover database;
启动 recover 于 12-8月 -13
使用通道 ORA_DISK_1
正在开始介质的恢复
介质恢复完成, 用时: 00:00:01
完成 recover 于 12-8月 -13
RMAN> alter database open;
已处理语句
RMAN>
RMAN> exit
恢复管理器完成。
E:\dul10>slqplus / as sysdba
'slqplus' 不是内部或外部命令，也不是可运行的程序
或批处理文件。
E:\dul10>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on 星期一 8月 12 22:46:36 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO

分区默认segment大小变化(64k—>8M)

在11.2.0.3(从11.2.0.2开始)创建分区表,每个分区默认大小为8M,是由_partition_large_extents参数控制,可以算是11.2.0.2开始的一个新特性,为了减少extent数量,提高分区表性能,而设置的一个参数,默认为true，即分区表的每个extent为8M,这里对于_partition_large_extents为true和false的情况进行了测试
_partition_large_extents=true

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> show parameter deferred_segment_creation
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> show parameter _partition_large_extents;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_partition_large_extents             string      FALSE
SQL> create table test_com_partition_1
  2  (
  3  name varchar2(4000) not null,
  4  aaaaa number not null,
  5  bbbbb varchar2(180) not null,
  6  ccccc varchar2(4000),
  7  constraint pk_test_com_partition_1 primary key(name)
  8  )
  9  partition by range(aaaaa) interval (1)
 10  subpartition by range (bbbbb)
 11  subpartition template
 12  (
 13  subpartition sp_2008 values less than ('2009') tablespace sp_2008,
 14  subpartition sp_2009 values less than ('2010') tablespace sp_2009,
 15  subpartition sp_2010 values less than ('2011') tablespace sp_2010,
 16  subpartition sp_2011 values less than ('2012') tablespace sp_2011,
 17  subpartition sp_2012 values less than ('2013') tablespace sp_2012,
 18  subpartition sp_2013 values less than ('2014') tablespace sp_2013,
 19  subpartition sp_2014 values less than ('2015') tablespace sp_2014,
 20  subpartition sp_2015 values less than ('2016') tablespace sp_2015,
 21  subpartition sp_2016 values less than ('2017') tablespace sp_2016,
 22  subpartition sp_2017 values less than ('2018') tablespace sp_2017,
 23  subpartition sp_2018 values less than ('2019') tablespace sp_2018,
 24  subpartition sp_2019 values less than ('2020') tablespace sp_2019,
 25  subpartition sp_2020 values less than ('2021') tablespace sp_2020,
 26  subpartition sp_2021 values less than ('2022') tablespace sp_2021,
 27  subpartition sp_2022 values less than ('2023') tablespace sp_2022,
 28  subpartition sp_2023 values less than ('2024') tablespace sp_2023,
 29  subpartition sp_2024 values less than ('2025') tablespace sp_2024,
 30  subpartition sp_2025 values less than ('2026') tablespace sp_2025,
 31  subpartition sp_max values less than (maxvalue) tablespace sp_max
 32  )
 33  (partition part_init values less than (1))
 34  enable row movement;
Table created.
--数据库延迟对象创建
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  where tablespace_name
   2 like 'SP%' group by TABLESPACE_NAME;
no rows selected
--只插入一个分区1,2013
SQL> insert into test_com_partition_1 values (lpad('xifenfei',3900,'wwww.xifenfei'),1,'2013',
   2 rpad('aaafdfafd',4000,'b'));
1 row created.
SQL> commit;
Commit complete.
--所有分区全部都创建了segment
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  where tablespace_name like 'SP%'
   2 group by TABLESPACE_NAME;
TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SP_2018                                           8
SP_2022                                           8
SP_2021                                           8
SP_2025                                           8
SP_2011                                           8
SP_2008                                           8
SP_MAX                                            8
SP_2020                                           8
SP_2012                                           8
SP_2010                                           8
SP_2024                                           8
SP_2019                                           8
SP_2015                                           8
SP_2014                                           8
SP_2013                                           8
SP_2023                                           8
SP_2017                                           8
SP_2016                                           8
SP_2009                                           8
19 rows selected.
SQL> begin
  2  for i in 3 .. 200 loop
  3  insert into test_com_partition_1 values (to_char(i)||lpad('xifenfei',3900,'wwww.xifenfei'),mod(i,5),
     '2013',rpad('xifenfei',4000,'www.xifenfei.com'));
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL procedure successfully completed.
--只是在2013的分区(1,子分区2013)中插入了对象,但是其他分区也都创建了segment(extent)
SQL>  select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  where tablespace_name
   2  like 'SP%' group by TABLESPACE_NAME;
TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SP_2018                                          32
SP_2021                                          32
SP_2022                                          32
SP_2008                                          32
SP_2011                                          32
SP_2025                                          32
SP_2010                                          32
SP_2012                                          32
SP_2020                                          32
SP_MAX                                           32
SP_2015                                          32
SP_2019                                          32
SP_2024                                          32
SP_2013                                          40
SP_2014                                          32
SP_2023                                          32
SP_2009                                          32
SP_2016                                          32
SP_2017                                          32
19 rows selected.
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2015';
PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
SYS_SUBP128                    SP_2015
SYS_SUBP148                    SP_2015
SYS_SUBP168                    SP_2015
SYS_SUBP188                    SP_2015
--因为在创建表语句中有partition part_init values less than (1)，隐藏之类对于小于1的分区没有子分区，只有PART_INIT_SP_2013
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2013';
PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
PART_INIT_SP_2013              SP_2013
SYS_SUBP126                    SP_2013
SYS_SUBP146                    SP_2013
SYS_SUBP166                    SP_2013
SYS_SUBP186                    SP_2013

_partition_large_extents=false

SQL> alter system set "_partition_large_extents"=false;
System altered.
SQL> show parameter _partition_large_extents
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_partition_large_extents             string      FALSE
SQL> drop table test_com_partition_1 purge;
Table dropped.
SQL> alter system set deferred_segment_creation=true;
System altered.
SQL> create table test_com_partition_1
  2  (
  3  name varchar2(4000) not null,
  4  aaaaa number not null,
  5  bbbbb varchar2(180) not null,
  6  ccccc varchar2(4000),
  7  constraint pk_test_com_partition_1 primary key(name)
  8  )
  9  partition by range(aaaaa) interval (1)
 10  subpartition by range (bbbbb)
 11  subpartition template
 12  (
 13  subpartition sp_2008 values less than ('2009') tablespace sp_2008,
 14  subpartition sp_2009 values less than ('2010') tablespace sp_2009,
 15  subpartition sp_2010 values less than ('2011') tablespace sp_2010,
 16  subpartition sp_2011 values less than ('2012') tablespace sp_2011,
 17  subpartition sp_2012 values less than ('2013') tablespace sp_2012,
 18  subpartition sp_2013 values less than ('2014') tablespace sp_2013,
 19  subpartition sp_2014 values less than ('2015') tablespace sp_2014,
 20  subpartition sp_2015 values less than ('2016') tablespace sp_2015,
 21  subpartition sp_2016 values less than ('2017') tablespace sp_2016,
 22  subpartition sp_2017 values less than ('2018') tablespace sp_2017,
 23  subpartition sp_2018 values less than ('2019') tablespace sp_2018,
 24  subpartition sp_2019 values less than ('2020') tablespace sp_2019,
 25  subpartition sp_2020 values less than ('2021') tablespace sp_2020,
 26  subpartition sp_2021 values less than ('2022') tablespace sp_2021,
 27  subpartition sp_2022 values less than ('2023') tablespace sp_2022,
 28  subpartition sp_2023 values less than ('2024') tablespace sp_2023,
 29  subpartition sp_2024 values less than ('2025') tablespace sp_2024,
 30  subpartition sp_2025 values less than ('2026') tablespace sp_2025,
 31  subpartition sp_max values less than (maxvalue) tablespace sp_max
 32  )
 33  (partition part_init values less than (1))
 34  enable row movement;
Table created.
SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments
   2 where tablespace_name like 'SP%' group by TABLESPACE_NAME;
no rows selected
SQL> insert into test_com_partition_1 values (lpad('xifenfei',3900,'wwww.xifenfei'),
   2 1,'2013',rpad('aaafdfafd',4000,'b'));
1 row created.
SQL> commit;
Commit complete.
SQL>  select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  where tablespace_name
   2 like 'SP%' group by TABLESPACE_NAME;
Tablespace           SUM(BYTES)/1024/1024
-------------------- --------------------
SP_2018                             .0625
SP_2021                             .0625
SP_2022                             .0625
SP_2008                             .0625
SP_2011                             .0625
SP_2025                             .0625
SP_2010                             .0625
SP_2012                             .0625
SP_2020                             .0625
SP_MAX                              .0625
SP_2015                             .0625
SP_2019                             .0625
SP_2024                             .0625
SP_2013                             .0625
SP_2014                             .0625
SP_2023                             .0625
SP_2009                             .0625
SP_2016                             .0625
SP_2017                             .0625
19 rows selected.
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2015';
Partition Name       Tablespace
-------------------- --------------------
SYS_SUBP328          SP_2015
SQL>  select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2013';
Partition Name       Tablespace
-------------------- --------------------
SYS_SUBP326          SP_2013
SQL> begin
  2  for i in 3 .. 2000 loop
  3  insert into test_com_partition_1 values (to_char(i)||lpad('xifenfei',3900,'wwww.xifenfei'),
     mod(i,5),'2013',rpad('xifenfei',4000,'www.xifenfei.com'));
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2015';
Partition Name       Tablespace
-------------------- --------------------
SYS_SUBP328          SP_2015
SYS_SUBP348          SP_2015
SYS_SUBP368          SP_2015
SYS_SUBP388          SP_2015
SQL> select PARTITION_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='SP_2013';
Partition Name       Tablespace
-------------------- --------------------
PART_INIT_SP_2013    SP_2013
SYS_SUBP326          SP_2013
SYS_SUBP346          SP_2013
SYS_SUBP366          SP_2013
SYS_SUBP386          SP_2013

通过测试证明,设置_partition_large_extents参数确实是能够控制分区表的extent大小,而且对于分区表,deferred_segment_creation虽然为true，但是在一个分区表中如果有一个子分区插入了记录,那么其他子分区会同时创建segment.对于数据量不多，而且数据大量集中在某几个分区,那强烈建议设置_partition_large_extents为false,节约空间.如果数据量较大,而且数据分布较为均匀,建议设置_partition_large_extents为true.另外对于分区的index也有同样的参数为_index_partition_large_extents

数据库中记录时间和现实中时间相互转换

数据库中记录时间和现实中时间相互转换（如同文件头的kcvfhcrt和v$datafile_header.CREATION_TIME相互转换）
以前写过类似文章，这里提供具体的sql转换语句数据文件的CREATION_TIME来源和算法

--十进制转换为时间
set serveroutput on
declare
v_yyyy number;
v_mm number;
v_dd number;
v_hh number;
v_mi number;
v_ss number;
begin
select floor(&&crt_num/32140800) into v_yyyy from dual;
select floor((&&crt_num-v_yyyy*32140800)/2678400) into v_mm from dual;
select floor((&&crt_num-v_yyyy*32140800-v_mm*2678400)/86400) into v_dd from dual;
select floor((&&crt_num-v_yyyy*32140800-v_mm*2678400-v_dd*86400)/3600) into v_hh from dual;
select floor((&&crt_num-v_yyyy*32140800-v_mm*2678400-v_dd*86400-v_hh*3600)/60) into v_mi from dual;
select (&&crt_num-v_yyyy*32140800-v_mm*2678400-v_dd*86400-v_hh*3600-v_mi*60) into v_ss from dual;
dbms_output.put_line((1988+v_yyyy)||'-'||(1+v_mm)||'-'||(1+v_dd)||' '||v_hh||':'||v_mi||':'||v_ss);
end;
/
--时间转换为十进制
select
((to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'YYYY'))-1988)*12*31*24*60*60) +
    ((to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'MM'))-1)*31*24*60*60) +
    (((to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'DD'))-1))*24*60*60) +
    (to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'HH24'))*60*60) +
    (to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'MI'))*60) +
    (to_number(to_char(to_date('&&v_date','yyyy-mm-dd hh24:mi:ss'),'SS')))
from dual;

plug pdb xml文件不正确错误提示

经过测试,发现pdb插入cdb的过程中,对于xml的错误提示功能非常强大,在简单的测试过程中,发现出tablespace部分不能正常提示正确值之外,其他都可以完美的提示正常值,根据他的提示去修改值即可,下面是测试过程中主要的错误值和正确值提示

<cid>2</cid>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65139: Mismatch between XML metadata file and data file
D:\PDB1_SYSTEM01.DBF for value of cid (2 in the plug XML file, 4 in the data
file)
<afn>16</afn>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65139: Mismatch between XML metadata file and data file
D:\PDB1_SYSTEM01.DBF for value of afn (16 in the plug XML file, 19 in the data
file)
<guid>E0408583F149457AE043161EA8C08888</guid>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65139: Mismatch between XML metadata file and data file
D:\PDB1_SYSTEM01.DBF for value of guid (E0408583F149457AE043161EA8C08888 in the
plug XML file, E0408583F149457AE043161EA8C0E5B7 in the data file)
<rdba>4094824</rdba>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65139: Mismatch between XML metadata file and data file
D:\PDB1_SYSTEM01.DBF for value of rdba (4094824 in the plug XML file, 4194824
in the data file)
<createscnbas>3283924</createscnbas>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65139: Mismatch between XML metadata file and data file
D:\PDB1_SYSTEM01.DBF for value of createscnbas (3283924 in the plug XML file,
3289484 in the data file)
<fcpsb>3289191</fcpsb>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65139: Mismatch between XML metadata file and data file
D:\PDB1_SYSTEM01.DBF for value of fcpsb (3289191 in the plug XML file, 3291031
in the data file)

对于表空间部分的这几个值，不能很好的验证，如果人工编辑，需要注意这些值，都可能出现类似错误ORA-65064,提示不太友好（但是测试中表空间名称不正确可以插入进去)

 <name>SYSAUX1</name>
 <type>0</type>
 <tsn>1</tsn>
 <status>1</status>
 <issft>0</issft>
<name>SYSTEM</name>
    <type>0</type>
    <tsn>1</tsn>
SQL> create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy ;
create pluggable database pdb1 using 'd:/pdb1_win_un.xml' nocopy
*
第 1 行出现错误:
ORA-65064: 插件 XML 文件的内容不正确

在正常的情况下,如果是做nocdb插入到cdb中或者是pdb的unplug/plug建议直接使用生成的xml文件,不需要人工去编辑它(如果要去修改它,你需要理解它的xml规则)
根据pdb的插入如此强大功能,提供给我们单独pdb数据库异常恢复很好的帮助,相关细节在后续章节揭晓,关于pdb的数据库一些异常恢复

ORACLE 12C 在datapump方面增强参数

在阅读ORACLE 12C datapump相关文档之时,发现有两个比较欣喜的参数LOGTIME和SQLFILE,鉴于他们是12C新增加参数,对他们的使用方法和用途进行简单说明
LOGTIME参数
该参数可以用于expdp/impdp,主要作用是记录执行步骤的开始时间,精确到微秒,使用语法为

LOGTIME=[NONE | STATUS | LOGFILE | ALL]
• NONE--No timestamps on status or log file messages (same as default)
• STATUS--Timestamps on status messages only
• LOGFILE--Timestamps on log file messages only
• ALL--Timestamps on both status and log file messages

该参数主要在我们对于一些数据库迁移升级项目使用datapump的时候,在测试阶段能够通过该参数发现哪一步执行时间较长,然后对其调优减少执行时间;另外一点就是可以通过做减法精确到具体的时间(毫米),我们可以知道我们的datapump主要耗时在哪一步,做到心中有数不慌

LOGTIME测试

SQL> conn chf/xifenfei@pdb
已连接。
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB                            READ WRITE NO
SQL> create directory temp as 'e:\';
目录已创建。
SQL> create table t_xifenfei as select * from dba_objects;
表已创建。
SQL> create index ind_t_xifenfei on t_xifenfei(object_id);
索引已创建。
C:\Users\XIFENFEI>expdp chf/xifenfei@pdb dumpfile=t_xifenfei.dmp tables=t_xifenf
ei logfile=t_xifenfei.log directory=temp REUSE_DUMPFILES=yes LOGTIME=all
Export: Release 12.1.0.1.0 - Production on 星期日 7月 14 20:11:24 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
14-7月 -13 20:11:35.961: 启动 "CHF"."SYS_EXPORT_TABLE_01":  chf/********@pdb dumpfile=t_xifenfei.dmp
 tables=t_xifenfei logfile=t_xifenfei.log directory=temp REUSE_DUMPFILES=yes LOGTIME=all
14-7月 -13 20:11:37.703: 正在使用 BLOCKS 方法进行估计...
14-7月 -13 20:11:40.636: 处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
14-7月 -13 20:11:40.825: 使用 BLOCKS 方法的总估计: 13 MB
14-7月 -13 20:11:48.802: 处理对象类型 TABLE_EXPORT/TABLE/TABLE
14-7月 -13 20:11:54.543: 处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
14-7月 -13 20:11:57.204: 处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
14-7月 -13 20:11:59.269: 处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
14-7月 -13 20:11:59.306: 处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/MARKER
14-7月 -13 20:12:36.563: . . 导出了 "CHF"."T_XIFENFEI"  10.36 MB   90865 行
14-7月 -13 20:12:37.527: 已成功加载/卸载了主表 "CHF"."SYS_EXPORT_TABLE_01"
14-7月 -13 20:12:37.533: ******************************************************************************
14-7月 -13 20:12:37.537: CHF.SYS_EXPORT_TABLE_01 的转储文件集为:
14-7月 -13 20:12:37.547:   E:\T_XIFENFEI.DMP
14-7月 -13 20:12:37.577: 作业 "CHF"."SYS_EXPORT_TABLE_01" 已于 星期日 7月 14 20:12:37 2013 elapsed 0 00:01:06 成功完成

SQLFILE参数
该参数可以用于impdp,主要作用是未真实在目标端执行导入的情况下,生成sql文件包含该dmp文件的所有ddl语句,使用语法为

SQLFILE=[directory_object:]file_name

注意事项:
1.directory_object可以不和impdp的DIRECTORY参数不一致,如果是一样,directory_object可以省略
2.SQLFILE文件必须写入到磁盘之上,不能写入到ASM中
3.SQLFILE和QUERY参数冲突,不能同时使用

SQLFILE测试

C:\Users\XIFENFEI>impdp chf/xifenfei@pdb dumpfile=t_xifenfei.dmp tables=t_xifenfei logfile=t_xifenfei.log
 directory=temp  sqlfile=t_xifenfei.sql
Import: Release 12.1.0.1.0 - Production on 星期日 7月 14 20:42:13 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Produc
tion
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
已成功加载/卸载了主表 "CHF"."SYS_SQL_FILE_TABLE_01"
启动 "CHF"."SYS_SQL_FILE_TABLE_01":  chf/********@pdb dumpfile=t_xifenfei.dmp ta
bles=t_xifenfei logfile=t_xifenfei.log directory=temp sqlfile=t_xifenfei.sql
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/MARKER
作业 "CHF"."SYS_SQL_FILE_TABLE_01" 已于 星期日 7月 14 20:42:25 2013 elapsed 0 00:00:08 成功完成

t_xifenfei.sql内容

-- CONNECT CHF
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "CHF"."T_XIFENFEI"
   (	"OWNER" VARCHAR2(128 BYTE),
	"OBJECT_NAME" VARCHAR2(128 BYTE),
	"SUBOBJECT_NAME" VARCHAR2(128 BYTE),
	"OBJECT_ID" NUMBER,
	"DATA_OBJECT_ID" NUMBER,
	"OBJECT_TYPE" VARCHAR2(23 BYTE),
	"CREATED" DATE,
	"LAST_DDL_TIME" DATE,
	"TIMESTAMP" VARCHAR2(19 BYTE),
	"STATUS" VARCHAR2(7 BYTE),
	"TEMPORARY" VARCHAR2(1 BYTE),
	"GENERATED" VARCHAR2(1 BYTE),
	"SECONDARY" VARCHAR2(1 BYTE),
	"NAMESPACE" NUMBER,
	"EDITION_NAME" VARCHAR2(128 BYTE),
	"SHARING" VARCHAR2(13 BYTE),
	"EDITIONABLE" VARCHAR2(1 BYTE),
	"ORACLE_MAINTAINED" VARCHAR2(1 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX "CHF"."IND_T_XIFENFEI" ON "CHF"."T_XIFENFEI" ("OBJECT_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" PARALLEL 1 ;
  ALTER INDEX "CHF"."IND_T_XIFENFEI" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/MARKER

ORACLE 12C datapump关于LOGTIME和SQLFILE参数

ORACLE 12C EM Express补充

在几个月前写过一篇关于《Enterprise Manager Database Express 12c 欣赏| 惜分飞》,最近在玩该东西的时候,发现ORACLE 12C的Enterprise Manager Database Express 12c相对于11G和10G的Enterprise Manager,从功能上说确实精简了很多,比如不支持在线查看AWR,不支持在线操作不备份,不支持对SCHEDULER的操作等等，减少了功能的同时也大大的降低了其使用难度，不用向以前以前还需要启动dbconsole,需要配置资料库等等一些繁琐的操作,还经常出现一些莫名其妙的问题不得不重建EM。预测几年之后EM Express的使用人说可能会增加(数据库升级到12C是一个因素,有些人毕竟不会购买GC)。在12C的Express版本中，默认情况下只需要在对应的pdb用户下执行如下操作即可启用EM Express
•设置http端口
exec DBMS_XDB_CONFIG.SETHTTPPORT(http_port_number);
•设置https端口
exec DBMS_XDB_CONFIG.SETHTTPSPORT(https_port_number);
注意：Each container must use a unique port for EM Express.
而且该操作是使用xdb组件开启对应端口用来通过浏览器http/https访问EM Express.监听的端口可以通过lsnrctl status查看,开启一个pdb的EM Express操作过程

未开启pdb库的监听状态如下

C:\Users\XIFENFEI>lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.1.0.1.0 – Production on 12-7月 -2013 21:2
2:32
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XIFENFEI-PC)(PORT=1521)))
LISTENER 的 STATUS
————————
别名                      LISTENER
版本                      TNSLSNR for 64-bit Windows: Version 12.1.0.1.0 – Produ
ction
启动日期                  12-7月 -2013 21:22:08
正常运行时间              0 天 0 小时 0 分 27 秒
跟踪级别                  off
安全性                    ON: Local OS Authentication
SNMP                      OFF
监听程序参数文件          E:\oracle\product\112~1.0\dbhome_1\NETWORK\ADMIN\liste
ner.ora
监听程序日志文件          E:\app\XIFENFEI\diag\tnslsnr\XIFENFEI-PC\listener\aler
t\log.xml
监听端点概要…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei-pc)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=xifenfei-pc)(PORT=5500))(Security=(
my_wallet_directory=E:\APP\XIFENFEI\admin\cdb\xdb_wallet))(Presentation=HTTP)(Se
ssion=RAW))
服务摘要..
服务 "CLRExtProc" 包含 1 个实例。
  实例 "CLRExtProc", 状态 UNKNOWN, 包含此服务的 3 个处理程序…
服务 "cdb" 包含 1 个实例。
  实例 "cdb", 状态 READY, 包含此服务的 1 个处理程序…
服务 "cdbXDB" 包含 1 个实例。
  实例 "cdb", 状态 READY, 包含此服务的 1 个处理程序…
服务 "pdb" 包含 1 个实例。
  实例 "cdb", 状态 READY, 包含此服务的 1 个处理程序…
命令执行成功

这里我们可以看到开启了一个默认的5500端口的EM Express,是属于root container

开启pdb的EM Expess

C:\Users\XIFENFEI>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on 星期五 7月 12 21:50:28 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
SQL> alter session set container=pdb;
会话已更改。
SQL> exec DBMS_XDB_CONFIG.SETHTTPPORT(5505);
PL/SQL 过程已成功完成。

C:\Users\XIFENFEI>lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.1.0.1.0 - Production on 12-7月 -2013 21:5
1:40
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XIFENFEI-PC)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名                      LISTENER
版本                      TNSLSNR for 64-bit Windows: Version 12.1.0.1.0 - Produ
ction
启动日期                  12-7月 -2013 21:22:08
正常运行时间              0 天 0 小时 29 分 35 秒
跟踪级别                  off
安全性                    ON: Local OS Authentication
SNMP                      OFF
监听程序参数文件          E:\oracle\product\112~1.0\dbhome_1\NETWORK\ADMIN\liste
ner.ora
监听程序日志文件          E:\app\XIFENFEI\diag\tnslsnr\XIFENFEI-PC\listener\aler
t\log.xml
监听端点概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei-pc)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=xifenfei-pc)(PORT=5500))(Security=(
my_wallet_directory=E:\APP\XIFENFEI\admin\cdb\xdb_wallet))(Presentation=HTTP)(Se
ssion=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei-pc)(PORT=5505))(Presentatio
n=HTTP)(Session=RAW))
服务摘要..
服务 "CLRExtProc" 包含 1 个实例。
  实例 "CLRExtProc", 状态 UNKNOWN, 包含此服务的 3 个处理程序...
服务 "cdb" 包含 1 个实例。
  实例 "cdb", 状态 READY, 包含此服务的 1 个处理程序...
服务 "cdbXDB" 包含 1 个实例。
  实例 "cdb", 状态 READY, 包含此服务的 1 个处理程序...
服务 "pdb" 包含 1 个实例。
  实例 "cdb", 状态 READY, 包含此服务的 1 个处理程序...
命令执行成功

这里可以看到监听中多了一个监听端口5505,是给pdb的EM Epress使用

EM Express大概功能截图
root container
em_express1
pdb container
em_express2
这里需要注意cdb级别的东西直接在root container就查看,涉及到pdb container级别的东西,需要登录到具体pdb的EM Express,例如查看表空间数据文件只能在pdb级别看,redo/controlfile等信息可以在cdb级别看

关闭EM Express

SQL> alter session set container=pdb;
 
会话已更改。
 
SQL> exec DBMS_XDB_CONFIG.SETHTTPPORT(0);
 
PL/SQL 过程已成功完成。
 
SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions 断开

C:\Users\XIFENFEI>lsnrctl status
 
LSNRCTL for 64-bit Windows: Version 12.1.0.1.0 - Production on 12-7月 -2013 23:3
8:30
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XIFENFEI-PC)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名                      LISTENER
版本                      TNSLSNR for 64-bit Windows: Version 12.1.0.1.0 - Produ
ction
启动日期                  12-7月 -2013 21:22:08
正常运行时间              0 天 2 小时 16 分 25 秒
跟踪级别                  off
安全性                    ON: Local OS Authentication
SNMP                      OFF
监听程序参数文件          E:\oracle\product\112~1.0\dbhome_1\NETWORK\ADMIN\liste
ner.ora
监听程序日志文件          E:\app\XIFENFEI\diag\tnslsnr\XIFENFEI-PC\listener\aler
t\log.xml
监听端点概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei-pc)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=xifenfei-pc)(PORT=5500))(Security=(
my_wallet_directory=E:\APP\XIFENFEI\admin\cdb\xdb_wallet))(Presentation=HTTP)(Se
ssion=RAW))
服务摘要..
服务 "CLRExtProc" 包含 1 个实例。
  实例 "CLRExtProc", 状态 UNKNOWN, 包含此服务的 3 个处理程序...
服务 "cdb" 包含 1 个实例。
  实例 "cdb", 状态 READY, 包含此服务的 1 个处理程序...
服务 "cdbXDB" 包含 1 个实例。
  实例 "cdb", 状态 READY, 包含此服务的 1 个处理程序...
服务 "pdb" 包含 1 个实例。
  实例 "cdb", 状态 READY, 包含此服务的 1 个处理程序...
命令执行成功

已经没有了对pdb监控的5505端口，证明关闭pdb的EM Express成功

11GR2升级到12CR1并插入CDB

ORACLE 12C已经发布了十多天,其中一个亮点就是pdb,而在12C之前的数据库没有pdb之说,也就是说如果要把以前的数据库升级到12C,并且想让该库变成一个pdb,那所要做的工作就是先需要升级数据库从12C之前版本升级到12C,然后把一个NO-CDB数据库PLUG到CDB中.本blog演示:在前段时间意外的释放出来ORACLE 11.2.0.4版本,利用该版本升级到12.1.0.1,并插入到一个cdb库中
ORACLE 12C升级版本要求
12C_UPGRADE_VERSION
11.2.0.4到12.1.0.1升级操作操作[升级整体参考文档1503653.1]
当前相关组件版本信息

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> show parameter name;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      ora11g
db_unique_name                       string      ora11g
global_names                         boolean     FALSE
instance_name                        string      ora11g
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      ora11g
SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
COMP_NAME                                VERSION                        STATUS
---------------------------------------- ------------------------------ ----------------------
OWB                                      11.2.0.4.0                     VALID
Oracle Application Express               3.2.1.00.12                    VALID
Spatial                                  11.2.0.4.0                     VALID
Oracle Multimedia                        11.2.0.4.0                     VALID
Oracle XML Database                      11.2.0.4.0                     VALID
Oracle Text                              11.2.0.4.0                     VALID
Oracle Expression Filter                 11.2.0.4.0                     VALID
Oracle Rules Manager                     11.2.0.4.0                     VALID
Oracle Workspace Manager                 11.2.0.4.0                     VALID
Oracle Database Catalog Views            11.2.0.4.0                     VALID
Oracle Database Packages and Types       11.2.0.4.0                     VALID
JServer JAVA Virtual Machine             11.2.0.4.0                     VALID
Oracle XDK                               11.2.0.4.0                     VALID
Oracle Database Java Packages            11.2.0.4.0                     VALID
OLAP Analytic Workspace                  11.2.0.4.0                     VALID
Oracle OLAP API                          11.2.0.4.0                     VALID
16 rows selected.

升级准备工作
执行Pre-Upgrade Utility,具体参考Note 884522.1 How to Download and Run Oracle’s Database Pre-Upgrade Utility

SQL> @/tmp/preupgrd.sql
Loading Pre-Upgrade Package...
Executing Pre-Upgrade Checks...
Pre-Upgrade Checks Complete.
      ************************************************************
Results of the checks are located at:
 /u02/app/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade.log
Pre-Upgrade Fixup Script (run in source database environment):
 /u02/app/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade_fixups.sql
Post-Upgrade Fixup Script (run shortly after upgrade):
 /u02/app/oracle/cfgtoollogs/ora11g/preupgrade/postupgrade_fixups.sql
      ************************************************************
         Fixup scripts must be reviewed prior to being executed.
      ************************************************************
      ************************************************************
                   ====>> USER ACTION REQUIRED  <<====
      ************************************************************
 The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
                    prior to attempting your upgrade.
            Failure to do so will result in a failed upgrade.
           You MUST resolve the above errors prior to upgrade
      ************************************************************

这里发生了改变,在12C之前版本直接显示需要修改的相关操作,12C把相关操作封装到了preupgrade_fixups.sql脚本，执行该脚本按照提示修复问题.
这里主要以下问题需要解决

ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;
@/u01/app/oracle/product/12.1/db_1/rdbms/admin/emremove.sql
@/u02/app/oracle/product/11.2/db_1/olap/admin/catnoamd.sql
EXECUTE dbms_stats.gather_dictionary_stats;

执行dbupgdiag.sql收集升级前信息
如果有异常核对相应的MOS文章修改,具体见Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]

dbua升级数据库
12C的dbua发生了不上变化,因为都是图形化界面,不做过多描述,贴上几幅区别较大图进行说明,关于12C的dbua变化更加详细信息请参考:Complete Checklist to Upgrade the Database to 12c Release 1 using DBUA [ID 1516557.1]
12C_U_2
12C_U_3
12C_U_4

升级后检查
执行postupgrade_fixups.sql脚本查看确定需要升级后处理事宜,因为是dbua升级数据库,很多问题已经自动修复,无需人工再次干预,例如timezone(14–>18)

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0
SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
COMP_NAME                                     VERSION                        STATUS
--------------------------------------------- ------------------------------ ----------------------
Oracle Application Express                    4.2.0.00.27                    VALID
OWB                                           11.2.0.4.0                     VALID
Spatial                                       12.1.0.1.0                     VALID
Oracle Multimedia                             12.1.0.1.0                     VALID
Oracle XML Database                           12.1.0.1.0                     VALID
Oracle Text                                   12.1.0.1.0                     VALID
Oracle Workspace Manager                      12.1.0.1.0                     VALID
Oracle Database Catalog Views                 12.1.0.1.0                     VALID
Oracle Database Packages and Types            12.1.0.1.0                     VALID
JServer JAVA Virtual Machine                  12.1.0.1.0                     VALID
Oracle XDK                                    12.1.0.1.0                     VALID
Oracle Database Java Packages                 12.1.0.1.0                     VALID
OLAP Analytic Workspace                       12.1.0.1.0                     VALID
Oracle OLAP API                               12.1.0.1.0                     VALID
14 rows selected.

升级前后oratab信息对比
dbua使用12C环境变量shell下执行,注意不要人工修改oratab记录,执行完会自动修改

--升级前
[oracle@xifenfei ~]$ grep ora11g /etc/oratab
ora11g:/u02/app/oracle/product/11.2/db_1:N:             # line added by Agent
--升级后
[oracle@xifenfei ~]$ grep ora11g /etc/oratab
ora11g:/u01/app/oracle/product/12.1/db_1:N:             # line added by Agent

到此,我们可以确定11.2.0.4已经顺利升级到12.1.0.1,升级过程比较顺利,但是升级时间比较长,很可能和我的机器配置有关

NO-CDB PLUG CDB
把11.2.0.4升级到12.1.0.1的数据库插入到一个CDB数据库中,让其成为CDB一部分
PLUG操作示意图
plug in a non-cdb to cdb

升级后数据库信息

SQL>  select cdb,NAME,dbid from v$database;
CDB NAME            DBID
--- --------- ----------
NO  ORA11G    4215674657
SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

创建XML元数据文件

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  801701888 bytes
Fixed Size                  2293496 bytes
Variable Size             314573064 bytes
Database Buffers          478150656 bytes
Redo Buffers                6684672 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> EXEC DBMS_PDB.DESCRIBE( pdb_descr_file => '/tmp/ora11g.xml');
PL/SQL procedure successfully completed.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

CDB数据库信息

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED

检查升级后数据库是否适合插入到该cdb

SQL> set serveroutput on;
 declare
         compat boolean := FALSE;
    begin
       compat := dbms_pdb.check_plug_compatibility(pdb_descr_file => '/tmp/ora11g.xml');
        if compat
        then
               dbms_output.put_line('Yes');
       else
               dbms_output.put_line('No');
      end if;
   end;SQL>   2    3    4    5    6    7    8    9   10   11
 12  /
No
PL/SQL procedure successfully completed.

因为是第一次插入所以显示是No,可以忽略该问题继续插入

插入no-cdb to cdb库

SQL> CREATE PLUGGABLE DATABASE ora11g USING '/tmp/ora11g.xml' NOCOPY;
Pluggable database created.
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
         5 ORA11G                         MOUNTED

根据官方文档描述,如果是第一次是no-cdb plug cdb,需要先open一次库

SQL> alter session set container=ora11g;
Session altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-24344: success with compilation error
SQL> !oerr ora 24344
24344, 00000, "success with compilation error"
// *Cause:  A sql/plsql compilation error occurred.
// *Action: Return OCI_SUCCESS_WITH_INFO along with the error code
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 ORA11G                         READ WRITE YES

出现ORA-24344,但是数据库正常open到read write模式,忽略该错误，继续执行

执行noncdb_to_pdb脚本

SQL> alter session set container=ora11g;
Session altered.
SQL> shutdown immediate
Pluggable Database closed.
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
--遇到Warning,脚本自动忽略该错误，继续执行,在最后该脚本编译的时候会修复该问题,原因很可能是某个plslq异常
SQL> alter pluggable database "&pdbname" open restricted;
old   1: alter pluggable database "&pdbname" open restricted
new   1: alter pluggable database "ORA11G" open restricted
Warning: PDB altered with errors.

同步pdb信息

[oracle@xifenfei ~]$ sqlplus sys/xifenfei@ora11g as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 03:05:42 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SYS% ora11g> alter pluggable database open restricted;
Pluggable database altered.
SYS% ora11g>  exec dbms_pdb.sync_pdb();
PL/SQL procedure successfully completed.
SYS% ora11g> alter pluggable database close immediate;
Pluggable database altered.
SYS% ora11g> alter pluggable database open;
Pluggable database altered.

确定no-cdb plug cdb 成功

SYS% ora11g> conn / as sysdba
Connected.
SYS% cdb1> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
         5 ORA11G                         READ WRITE NO

到这里已经完全完成了11.2.0.4数据库插入到12.1.0.1中,实现把11GR2转化为CDB数据库中的一个PDB

使用bbed解决ORA-01178 file N created before last CREATE CONTROLFILE, cannot recreate

在一些情况下,因为某种原因数据库中的某个数据文件丢失,然后重建控制文件(该文件不存在,重建控制文件肯定不包含该数据文件),这个时候丢失的数据文件所有的归档都存在,尝试alter database create datafile命令创建该数据文件并且利用所有的归档来恢复该数据文件,可是因为控制文件已经重建,无法完成数据文件的重新创建（报ORA-01178错误)
出现这个错误的原因是因为数据文件的创建时间不能早于你的控制文件的创建时间,现在你重建了控制文件,那么很明显你的数据文件的创建时间要早于你的控制文件创建时间,数据库从恢复的原理上判断,你这样做法是违法,终止该操作.解决该问题的主要思路有三个:
1.修改控制文件,让数据库认为你的控制文件创建时间在该数据文件创建之前,通过该欺骗的方法来实现create datafile
2.人工创建好异常数据文件,然后让数据库开始应用归档日志,使得归档的内容能够重现到人工创建的数据文件中,从而找回归档中内容
3.使用logminer挖归档,对应成sql,然后在其他库中重现，此方法只能对于归档比较少的库
因为3比较简单,不做任何操作,因为控制文件结果不是非常了解,修改了N次还是报异常,最后放弃,使用方法2,人工重构文件完成该项工作
模拟环境

SQL> create tablespace ORA01178 DATAFILE 'E:\ORACLE\ORADATA\XIFENFEI\ORA01178.01.DBF'
   2 SIZE 10M AUTOEXTEND ON MAXSIZE 30G;
Tablespace created.
SQL> DROP TABLE T_XIFENFEI;
Table dropped.
SQL> CREATE TABLE T_XIFENFEI TABLESPACE ORA01178
  2  AS
  3  SELECT * FROM DBA_OBJECTS;
Table created.
SQL> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\oracle\product\11.2.0\dbhome_1\RDBMS
Oldest online log sequence     123
Next log sequence to archive   125
Current log sequence           125
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\oracle\product\11.2.0\dbhome_1\RDBMS
Oldest online log sequence     127
Next log sequence to archive   129
Current log sequence           129
SQL> INSERT INTO T_XIFENFEI
  2  SELECT * FROM DBA_OBJECTS;
68078 rows created.
SQL> /
68078 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='T_XIFENFEI' AND OWNER='SYS';
BYTES/1024/1024
---------------
             24
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
    204234
SQL> SHUTDOWN ABORT
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1385052 bytes
Variable Size             331353508 bytes
Database Buffers           79691776 bytes
Redo Buffers                6053888 bytes
SQL>  CREATE CONTROLFILE REUSE DATABASE "XIFENFEI" NORESETLOGS  ARCHIVELOG
  2        MAXLOGFILES 16
  3        MAXLOGMEMBERS 3
  4        MAXDATAFILES 100
  5        MAXINSTANCES 8
  6        MAXLOGHISTORY 292
  7    LOGFILE
  8      GROUP 1 'E:\ORACLE\ORADATA\XIFENFEI\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9      GROUP 2 'E:\ORACLE\ORADATA\XIFENFEI\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10      GROUP 3 'E:\ORACLE\ORADATA\XIFENFEI\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11    DATAFILE
 12      'E:\ORACLE\ORADATA\XIFENFEI\SYSTEM01.DBF',
 13      'E:\ORACLE\ORADATA\XIFENFEI\SYSAUX01.DBF',
 14      'E:\ORACLE\ORADATA\XIFENFEI\UNDOTBS01.DBF',
 15      'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF',
 16      'E:\ORACLE\ORADATA\XIFENFEI\UNDO01.DBF',
 17      'E:\ORACLE\ORADATA\XIFENFEI\CZUM01.DBF',
 18      'E:\ORACLE\ORADATA\XIFENFEI\DUL01.DBF',
 19      'E:\ORACLE\ORADATA\XIFENFEI\DD_DUL.DBF'
 20    CHARACTER SET ZHS16GBK
 21    ;
Control file created.
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\XIFENFEI\SYSTEM01.DBF'
SQL> RECOVER DATABASE;
Media recovery complete.
SQL> ALTER DATABASE OPEN;
Database altered.

尝试创建数据文件,报ORA-01178

SQL> alter database create datafile  9 as 'E:\ORACLE\ORADATA\XIFENFEI\ORA01178.01.DBF'
;
alter database create datafile  9 as 'E:\ORACLE\ORADATA\XIFENFEI\ORA01178.01.DBF'
*
ERROR at line 1:
ORA-01178: file 9 created before last CREATE CONTROLFILE, cannot recreate
ORA-01111: name for data file 9 is unknown - rename to correct file
ORA-01110: data file 9:
'E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00009'
[root@vrh2 ~]# oerr ora 1178
01178, 00000, "file %s created before last CREATE CONTROLFILE, cannot recreate"
// *Cause:  Attempted to use ALTER DATABASE CREATE DATAFILE to recreate a
//          datafile that existed at the last CREATE CONTROLFILE command.
//          The information needed to recreate the file was lost with the
//          control file that existed when the file was added to the database.
// *Action: Find a backup of the file, and recover it. Do incomplete recovery
//          to time before file was originally created.

官方提示,因为控制文件的创建时间比你现在要创建的数据文件时间晚,所以不能完成在此数据文件基础之上完成数据文件的创建工作,如果解决该问题,使用备份的数据文件还原或者使用备份的控制文件然后创建数据文件

继续分析该数据文件

SQL> select name from v$datafile where status='RECOVER';
NAME
--------------------------------------------------------------
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00009
SQL> select FILE#,TS# from v$datafile where status='RECOVER';
     FILE#        TS#
---------- ----------
         9          8
SQL> SELECT FILE# from v$datafile where ts#=8;
     FILE#
----------
         9
SQL> select name from v$tablespace where ts#=8;
NAME
------------------------------
ORA01178
--该表空间只有一个数据文件,不好构造数据该数据文件,增加一数据文件,用于构造
--查询数据文件创建信息
SQL> select ts#,file#,RELFILE#,BLOCKS,crscnwrp,crscnbas from file$;
       TS#      FILE#   RELFILE#     BLOCKS   CRSCNWRP   CRSCNBAS
---------- ---------- ---------- ---------- ---------- ----------
         0          1          1      89600          0          9
         1          2          2      76800          0       2130
         2          3          3      25600          0       3333
         4          4          4        640          0      18379
         5          5          5       1280          0     890563
         6          6          6       1280          0     944810
         7          7          7       1280          0    1289586
         7          8          8       1280          0    1848530
         8          9          9       1280          0    1880412
--创建初始化大小相同在相同表空间数据文件
SQL> alter tablespace ora01178 add datafile 'E:\ORACLE\ORADATA\XIFENFEI\DD_1178.02.dbf' size 10m;
Tablespace altered.
SQL> select ts#,file#,RELFILE#,BLOCKS,crscnwrp,crscnbas from file$;
       TS#      FILE#   RELFILE#     BLOCKS   CRSCNWRP   CRSCNBAS
---------- ---------- ---------- ---------- ---------- ----------
         0          1          1      89600          0          9
         1          2          2      76800          0       2130
         2          3          3      25600          0       3333
         4          4          4        640          0      18379
         5          5          5       1280          0     890563
         6          6          6       1280          0     944810
         7          7          7       1280          0    1289586
         7          8          8       1280          0    1848530
         8          9          9       1280          0    1880412
         8         10         10       1280          0    1901583

到目前,我们创建了一个和故障数据文件在同一个表空间,而且同样初始化大小的数据文件

CP新数据文件
E:\ORACLE\ORADATA\XIFENFEI\DD_1178.02.dbf 为E:\oracle\oradata\XIFENFEI\dd_ora1178.02.dbf

bbed修改相关值
因为在win中block 2其实为数据库的block 1,依次类推

m /x 01004002 block 2 offset 4
m /x 09 block 2 offset 52
m /x 5cb11c00 block 2 offset 100
m /x 09 block 2 offset 368
m /x 5cb11c00 offset 484
…………
m /x 02004002 block 3 offset 4
m /x 09 block 3 offset 20
sum apply block 3
…………

online数据文件

SQL> alter database rename file 'E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00009'
   2 to 'E:\oracle\oradata\XIFENFEI\dd_ora1178.02.dbf';
Database altered.
SQL> recover datafile 9;
ORA-00279: change 1880412 generated at 07/05/2013 20:59:44 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000125_0805223583.0001
ORA-00280: change 1880412 for thread 1 is in sequence #125
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\product\11.2.0\dbhome_1\RDBMS\archivelog\ARC0000000125_0805223583.0001
ORA-00279: change 1880816 generated at 07/05/2013 11:51:27 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000126_0805223583.0001
ORA-00280: change 1880816 for thread 1 is in sequence #126
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\product\11.2.0\dbhome_1\RDBMS\archivelog\ARC0000000126_0805223583.0001
ORA-00279: change 1880819 generated at 07/05/2013 11:51:29 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000127_0805223583.0001
ORA-00280: change 1880819 for thread 1 is in sequence #127
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\product\11.2.0\dbhome_1\RDBMS\archivelog\ARC0000000127_0805223583.0001
ORA-00279: change 1880824 generated at 07/05/2013 11:51:33 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000128_0805223583.0001
ORA-00280: change 1880824 for thread 1 is in sequence #128
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\product\11.2.0\dbhome_1\RDBMS\archivelog\ARC0000000128_0805223583.0001
ORA-00279: change 1880827 generated at 07/05/2013 11:51:34 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000129_0805223583.0001
ORA-00280: change 1880827 for thread 1 is in sequence #129
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\product\11.2.0\dbhome_1\RDBMS\archivelog\ARC0000000129_0805223583.0001
ORA-00279: change 1901078 generated at 07/05/2013 11:57:44 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000130_0805223583.0001
ORA-00280: change 1901078 for thread 1 is in sequence #130
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\product\11.2.0\dbhome_1\RDBMS\archivelog\ARC0000000130_0805223583.0001
Log applied.
Media recovery complete.
SQL> alter database datafile 9 online;
Database altered.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
    204234
SQL> SELECT BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='T_XIFENFEI' AND OWNER='SYS';
BYTES/1024/1024
---------------
             24

从这里可以完整的看到,人工利用datafile 10,构造了datafile 9,然后应用归档,完美的找回了归档中的内容.该操作是一个复杂的过程,如果你需要技术支持请联系:
Phone:17813235971    Q Q:107644445    E-Mail:dba@xifenfei.com