Author Archives: 惜分飞

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

Posted on by

最近在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)

Posted on by

在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.orasos.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.orasos.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

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

Posted on by

数据库中记录时间和现实中时间相互转换（如同文件头的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文件不正确错误提示

Posted on by

经过测试,发现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的数据库一些异常恢复