ORACLE 12C – 第3页 – Database SOS

ORCLE 12C 增加列,无默认值

对11gR2比较熟悉的朋友应该比较清楚,在该版本中引入了一个新的特性,能够快速的增加一个新列,具体见Oracle 11g增加列,并带默认值的新特性,但是这个功能在该版本中总有个不足,需要设置默认值,在有些情况下,有些列就是不需要默认值,在12C的版本中,解决了这个鸡肋,能够快速增加一个列而且可以是不指定默认值(默认值为NULL)
数据库12C版本

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

创建模拟表

SQL> create table t_xifenfei(id number,name varchar2(20));
Table created.
SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(20)
SQL> insert into t_xifenfei values (1,'www.xifenfei.com');
1 row created.
SQL> insert into t_xifenfei values (2,'www.xifenfei.com');
1 row created.
SQL> insert into t_xifenfei values (3,'www.xifenfei.com');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_xifenfei;
        ID NAME
---------- --------------------
         1 www.xifenfei.com
         2 www.xifenfei.com
         3 www.xifenfei.com

第一次dump block

SQL> select   rowid,
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3   dbms_rowid.rowid_block_number(rowid)blockno,
  4   dbms_rowid.rowid_row_number(rowid) rowno
  5   from t_xifenfei ;
ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAEy3AAEAAAAGGAAA          4        390          0
AAAEy3AAEAAAAGGAAB          4        390          1
AAAEy3AAEAAAAGGAAC          4        390          2
SQL> alter system dump datafile 4 block 390;
System altered.
--dump block
block_row_dump:
tab 0, row 0, @0x1f81
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f6a
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 2, @0x1f53
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
end_of_block_dump

增加不含默认值列

SQL> alter table t_xifenfei add c_xff varchar2(100);
Table altered.
SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(20)
 C_XFF                                              VARCHAR2(100)
--证明增加列无默认值
SQL> insert into t_xifenfei values(4,'www.xifenfei.com','www.orasos.com');
1 row created.
SQL> commit;
Commit complete.

第二次dump block

SQL> alter system checkpoint;
System altered.
SQL>  alter system dump datafile 4 block 390;
System altered.
--block dump
block_row_dump:
tab 0, row 0, @0x1f81
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f6a
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 2, @0x1f53
tl: 23 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 3, @0x1f2d
tl: 38 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 05
col  1: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
col  2: [14]  77 77 77 2e 6f 72 61 73 6f 73 2e 63 6f 6d
end_of_block_dump

从这里可以明显的看出来,前面的三条记录只有2列,但是四条记录有3列,证明使用了11gR2的新特性,这里可以使用null的默认值,证明比以往版本新特性增强.

ORACLE 12C Invisible Columns and Column Ordering

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE 12C Invisible Columns and Column Ordering

在ORACLE 12C中有了Invisible Columns的概念,就是在表中真实的存在该列,但是通过设置Invisible导致该列不可显示.官方说明:

The property of whether a column is visible can be controlled by the user.
Invisible columns are not seen unless specified explicitly in the SELECT list.
Any generic access of a table (such as a SELECT * FROM table or a DESCRIBE)
  will not show invisible columns.

数据库版本

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

创建含INVISIBLE列表

SQL> CREATE TABLE t_xifenfei (a number, b number INVISIBLE, c number);
Table created.
SQL> desc t_xifenfei
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ---------------------------
 A                                                              NUMBER
 C                                                              NUMBER
SQL>  select column_name,HIDDEN_COLUMN,COLUMN_ID from user_TAB_COLs  where TABLE_NAME='T_XIFENFEI';
COLUMN_NAM HID  COLUMN_ID
---------- --- ----------
A          NO           1
B          YES
C          NO           2

通过观察可以发现INVISIBLE列在一般的查询中不显示,在USER_TAB_COLS的视图中显示:HIDDEN_COLUMN为YES而且COLUMN_ID为空

设置INVISIBLE列为VISIBLE

SQL> ALTER TABLE t_xifenfei MODIFY (b VISIBLE);
Table altered.
SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 C                                                  NUMBER
 B                                                  NUMBER
SQL> select column_name,HIDDEN_COLUMN,COLUMN_ID from user_TAB_COLs  where TABLE_NAME='T_XIFENFEI';
COLUMN_NAM HID  COLUMN_ID
---------- --- ----------
A          NO           1
B          NO           3
C          NO           2

当设置为VISIBLE时HIDDEN_COLUMN为YES而且COLUMN_ID为递增值

修改列展示顺序(Column Ordering)

SQL> ALTER TABLE t_xifenfei MODIFY (a invisible);
Table altered.
SQL> ALTER TABLE t_xifenfei MODIFY (a visible);
Table altered.
SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C                                                  NUMBER
 B                                                  NUMBER
 A                                                  NUMBER
SQL> select column_name,HIDDEN_COLUMN,COLUMN_ID from user_TAB_COLs  where TABLE_NAME='T_XIFENFEI';
COLUMN_NAM HID  COLUMN_ID
---------- --- ----------
A          NO           3
B          NO           2
C          NO           1

通过INVISIBLE和VISIBLE相关操作,实现A列从头移尾,实现列的Column Ordering效果.
补充说明:在以前的blog中,提供了修改col$基表的方法(通过修改col$.col#改变列展示顺序)来实现列的顺序修改,相对于这种方法来说,修改数据字典的方法风险太大,需要非常谨慎,而且不被ORACLE SUPPORT

ORACLE 12C 支持multiple partitions同时操作

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE 12C 支持multiple partitions同时操作

ORACLE 12C在分区维护方面有了不少的增强,在12C的beta版本中已经支持多分区的add/truncate/drop/merge操作,大大的提高了分区维护的效率.
数据库版本

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

在FF PDB中创建xff用户

SQL> alter session set container=ff;
Session altered.
SQL> create user xff identified by xifenfei;
User created.
SQL> grant dba to xff;
Grant succeeded.
SQL> conn xff/xifenfei@ff
Connected.
SQL> show con_name;
CON_NAME
------------------------------
FF

创建分区表RANGE PARTITIONS

SQL> CREATE TABLE t_xifenfei
  2  (name varchar2(100),time_id DATE)
  3  partition by range(time_id)
  4  (partition xff_2006 values less than (TO_DATE('01-01-2007','dd-MM-yyyy')),
  5  partition xff_2007 values less than (TO_DATE('01-01-2008','dd-MM-yyyy')),
  6  partition xff_2008 values less than (TO_DATE('01-01-2009','dd-MM-yyyy')),
  7  partition xff_2009 values less than (TO_DATE('01-01-2010','dd-MM-yyyy')));
Table created.
SQL> SET LONG 30
SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';
PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00'
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00'
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00'
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'

ADD 多个分区

SQL> ALTER TABLE t_xifenfei ADD
  2    PARTITION XFF_2010 VALUES LESS THAN (TO_DATE('01-01-2011','dd-MM-yyyy')),
  3    PARTITION XFF_2011 VALUES LESS THAN (TO_DATE('01-01-2012','dd-MM-yyyy')),
  4    PARTITION XFF_2012 VALUES LESS THAN (TO_DATE('01-01-2013','dd-MM-yyyy'));
Table altered.
SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';
PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00'
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00'
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00'
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00'
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00'
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00'
7 rows selected.

Split多个分区

SQL> ALTER TABLE t_xifenfei  split PARTITION  XFF_2012 INTO
  2    (PARTITION XFF_2012_03 VALUES LESS THAN (TO_DATE('01-03-2012','dd-MM-yyyy')),
  3    PARTITION XFF_2012_06 VALUES LESS THAN (TO_DATE('01-06-2012','dd-MM-yyyy')),
  4    PARTITION XFF_2012_09 VALUES LESS THAN (TO_DATE('01-09-2012','dd-MM-yyyy')),
  5    PARTITION XFF_2012);
Table altered.
SQL>  select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';
PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00'
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00'
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00'
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00'
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00'
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00'
XFF_2012_03                    TO_DATE(' 2012-03-01 00:00:00'
XFF_2012_06                    TO_DATE(' 2012-06-01 00:00:00'
XFF_2012_09                    TO_DATE(' 2012-09-01 00:00:00'
10 rows selected.

插入分区数据

SQL>INSERT INTO t_xifenfei VALUES('www.xifenfei.com',SYSDATE-100);
1 row created.
SQL> INSERT INTO t_xifenfei VALUES('www.xifenfei.com',SYSDATE-200);
1 row created.
SQL> INSERT INTO t_xifenfei VALUES('www.xifenfei.com',SYSDATE-300);
1 row created.
SQL>  INSERT INTO t_xifenfei VALUES('www.xifenfei.com',SYSDATE-10);
1 row created.
SQL> commit;
Commit complete.
SQL> col name for a20
SQL> select * from t_xifenfei;
NAME                 TIME_ID
-------------------- ---------
www.xifenfei.com     17-FEB-12
www.xifenfei.com     27-MAY-12
www.xifenfei.com     04-SEP-12
www.xifenfei.com     03-DEC-12
SQL>select * from t_xifenfei PARTITION(XFF_2012_03);
NAME                 TIME_ID
-------------------- ---------
www.xifenfei.com     17-FEB-12
SQL> select * from t_xifenfei PARTITION(XFF_2012_06);
NAME                 TIME_ID
-------------------- ---------
www.xifenfei.com     27-MAY-12
SQL>  select * from t_xifenfei PARTITION(XFF_2012_09);
no rows selected
SQL>  select * from t_xifenfei PARTITION(XFF_2012);
NAME                 TIME_ID
-------------------- ---------
www.xifenfei.com     04-SEP-12
www.xifenfei.com     03-DEC-12

TRUNCATE 多个分区

SQL> Alter table t_xifenfei truncate partitions XFF_2012_03, XFF_2012_06, XFF_2012_09;
Table truncated.
--剩下两条记录存在于XFF_2012中
SQL> select * from t_xifenfei;
NAME                 TIME_ID
-------------------- ---------
www.xifenfei.com     04-SEP-12
www.xifenfei.com     03-DEC-12
SQL> SELECT SUBOBJECT_NAME,object_id,data_object_id from user_objects where SUBOBJECT_NAME like 'XFF_2012_0%';
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
XFF_2012_09                         90603          90603    <---为什么没有变
XFF_2012_06                         90602          90606
XFF_2012_03                         90601          90605
SQL> select PARTITION_NAME,HIGH_VALUE,SEGMENT_CREATED FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';
PARTITION_NAME                 HIGH_VALUE                     SEGM
------------------------------ ------------------------------ ----
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00' NO
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00' NO
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00' NO
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00' NO
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00' NO
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00' NO
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00' YES
XFF_2012_03                    TO_DATE(' 2012-03-01 00:00:00' YES
XFF_2012_06                    TO_DATE(' 2012-06-01 00:00:00' YES
XFF_2012_09                    TO_DATE(' 2012-09-01 00:00:00' NO
--XFF_2012_09因为块延迟创建,没有segment导致truncate对应的dataobj#不变
10 rows selected.

DROP 多个分区

SQL>  Alter table t_xifenfei DROP  partitions XFF_2012_03, XFF_2012_06, XFF_2012_09;
Table altered.
SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';
PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2006                       TO_DATE(' 2007-01-01 00:00:00'
XFF_2007                       TO_DATE(' 2008-01-01 00:00:00'
XFF_2008                       TO_DATE(' 2009-01-01 00:00:00'
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00'
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00'
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00'
7 rows selected.

MERGE 多分区

SQL> Alter table t_xifenfei merge partitions XFF_2006, XFF_2007, XFF_2008 into partition XFF_OLD;
Table altered.
SQL> select PARTITION_NAME,HIGH_VALUE FROM USER_TAB_PARTITIONS where table_name='T_XIFENFEI';
PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
XFF_2009                       TO_DATE(' 2010-01-01 00:00:00'
XFF_2010                       TO_DATE(' 2011-01-01 00:00:00'
XFF_2011                       TO_DATE(' 2012-01-01 00:00:00'
XFF_2012                       TO_DATE(' 2013-01-01 00:00:00'
XFF_OLD                        TO_DATE(' 2009-01-01 00:00:00'

本测试是基于Range partitions进行,其实在ORACLE 12C中对于分区表的维护做了比较大的增强,上面试验的多分区操作,也支持List partitions和subpartitions.ddl一次性操作多个分区,给分区经常做维护的DBA来说,带来了不少的方便,省去了很多重复行工作.

Enterprise Manager Database Express 12c 欣赏

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Enterprise Manager Database Express 12c 欣赏

12C里面不再提供完整的Enterprise Manager,而是提供了Enterprise Manager Database Express 12c,功能在以前的EM基础之上有了很大的折扣,这里贴几张图出来供大家欣赏

以前的版本中,Enterprise Manager是通过dbconsole来控制的,现在的版本直接在监听中注册端口来实现,默认访问为:http://xifenfei:5500/em

[oracle@xifenfei ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.0.2        on 13-DEC-2012 15:37:35
Copyright (c) 1991, 2012, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.0.2
Start Date                12-DEC-2012 22:31:55
Uptime                    0 days 17 hr. 5 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/xifenfei/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=5500))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ff" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xifenfei" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "lx1" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "lx2" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xffXDB" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xifenfei" has 1 instance(s).
  Instance "xifenfei", status READY, has 1 handler(s) for this service...
Service "xifenfeiXDB" has 1 instance(s).
  Instance "xifenfei", status READY, has 1 handler(s) for this service...
The command completed successfully

ORACLE 12C 依然支持 bbed

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE 12C 依然支持 bbed

ORACLE 12C的测试版出来了,对于习惯了使用bbed(ORACLE 手术刀)的人来说,12C是否继续被支持是一个很让人关注的问题,通过本实验测试,12C继续支持bbed,很多艰难的数据库恢复依然可以通过bbed来实现,也从侧面说明,ORACLE 12C在块的结构上还依然和以往版本相似
数据库信息

[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.0.2 Beta on Thu Dec 13 09:55:35 2012
Copyright (c) 1982, 2012, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$datafile where rownum=1;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/xifenfei/system01.dbf
SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit
With the Partitioning, OLAP, Data Mining and Real Application Testing options

12C中编译bbed

[oracle@xifenfei bbed_64]$ ls -l
total 32
-rw-r--r-- 1 oracle oinstall  8704 Apr 20  2010 bbedus.msb
-rw-r--r-- 1 oracle oinstall 10270 Jul 25  2000 bbedus.msg
-rw-r--r-- 1 oracle oinstall  3976 Apr 20  2010 sbbdpt.o
-rw-r--r-- 1 oracle oinstall  3306 Apr 20  2010 ssbbded.o
[oracle@xifenfei ~]$ cd /u01/app/oracle/product/12.1/db_1/rdbms/lib
[oracle@xifenfei lib]$ cp ~/bbed_64/s* ./
[oracle@xifenfei lib]$ cd ../
[oracle@xifenfei rdbms]$ cd /u01/app/oracle/product/12.1/db_1/rdbms/mesg
[oracle@xifenfei mesg]$ cp ~/bbed_64/bbedus.ms* ./
[oracle@xifenfei ~]$ cd /u01/app/oracle/product/12.1/db_1/rdbms/lib
[oracle@xifenfei lib]$ make -f ins_rdbms.mk /u01/app/oracle/product/12.1/db_1/rdbms/lib/bbed
Linking BBED utility (bbed)
rm -f /u01/app/oracle/product/12.1/db_1/rdbms/lib/bbed
/u01/app/oracle/product/12.1/db_1/bin/orald -o /u01/app/oracle/product/12.1/db_1/rdbms/lib/bbed
…………
/u01/app/oracle/product/12.1/db_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/12.1/db_1/lib
[oracle@xifenfei lib]$ mv bbed /u01/app/oracle/product/12.1/db_1/bin/

测试12C中bbed

[oracle@xifenfei ~]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Dec 13 09:58:16 2012
Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/u01/app/oracle/oradata/xifenfei/system01.dbf'
        FILENAME        /u01/app/oracle/oradata/xifenfei/system01.dbf
BBED> show
        FILE#           0
        BLOCK#          1
        OFFSET          0
        DBA             0x00000000 (0 0,1)
        FILENAME        /u01/app/oracle/oradata/xifenfei/system01.dbf
        BIFILE          bifile.bbd
        LISTFILE
        BLOCKSIZE       8192
        MODE            Browse
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No
BBED> map
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 1                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header
 struct kcvfh, 1112 bytes                   @0
 ub4 tailchk                                @8188
BBED> p kcvfhbfh
struct kcvfhbfh, 20 bytes                   @0
   ub1 type_kcbh                            @0        0x0b
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x00400001
   ub4 bas_kcbh                             @8        0x00000000
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x21a8
   ub2 spare3_kcbh                          @18       0x0000
BBED> set block 1233
        BLOCK#          1233
BBED> map
 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0)
 Block: 1233                                  Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Index Leaf)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdxle, 32 bytes                     @92
 sb2 kd_off[517]                            @124
 ub1 freespace[302]                         @1158
 ub1 rowdata[6660]                          @1460
 ub4 tailchk                                @8188

ORACLE 12C move datafile测试

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE 12C move datafile测试

通过一段时间的宣传,很多人都知道了ORACLE 12C可以实现在线move数据文件,使得我们从以前先offline相关文件任何系统级别mv,然后在rename file方便了很多。该功能的强大之处在于:
1.可以在库open的情况下move system表空间文件
2.对于其他表空间的数据文件move不用offline(意味着不用停业务)
3.大大简化了以前的操作步骤,很多初级dba对于原来的操作方法不理解,经常导致datafile最终异常
move datafile语法

move datafile之前操作

[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.0.2 Beta on Thu Dec 13 05:57:18 2012
Copyright (c) 1982, 2012, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select *from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0
SQL> alter session set container = ff;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
FF
SQL> col name for a65
SQL> set lines 134
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- -----------------------------------------------------------------
         5 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
        16 /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
        17 /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
        18 /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
        19 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
SQL> !ls -l /u01/app/oracle/oradata/xifenfei/xff*
-rw-r----- 1 oracle oinstall 365699072 Dec 13 05:55 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
-rw-r----- 1 oracle oinstall   5251072 Dec 13 05:55 /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
-rw-r----- 1 oracle oinstall 597696512 Dec 13 05:55 /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
-rw-r----- 1 oracle oinstall 283123712 Dec 13 05:55 /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
-rw-r----- 1 oracle oinstall  20979712 Dec 12 21:28 /u01/app/oracle/oradata/xifenfei/xfftemp01.dbf
/u01/app/oracle/oradata/xifenfei/xff:
total 0

执行move datafile操作

SQL> select sid from v$mystat where rownum=1;
       SID
----------
       259
SQL> alter database move datafile 16 to '/u01/app/oracle/oradata/xifenfei/xff/system01.dbf';
Database altered.
--new session
SQL> select con_id,sid,event from v$session where sid=259;
    CON_ID        SID EVENT
---------- ---------- -----------------------------------------------
         1        259 db file single write
SQL> /
    CON_ID        SID EVENT
---------- ---------- ------------------------------------------------
         1        259 db file sequential read
SQL> /
    CON_ID        SID EVENT
---------- ---------- -------------------------------------------------
         1        259 db file single write
--通过新会话监控等待事件发现,move datafile的主要等待是
--db file sequential read和db file single write
--继续执行move datafile
SQL> alter database move datafile
  2  '/u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf' to
  3  '/u01/app/oracle/oradata/xifenfei/xff/sysaux01.dbf';
Database altered.
SQL> alter database move datafile 18 to
  2  '/u01/app/oracle/oradata/xifenfei/xff/users01.dbf';
Database altered.
SQL> alter database move datafile 19 to
  2  '/u01/app/oracle/oradata/xifenfei/xff/example01.dbf';
Database altered.
SQL>  select file#,name from v$datafile;
     FILE# NAME
---------- -----------------------------------------------------------------
         5 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
        16 /u01/app/oracle/oradata/xifenfei/xff/system01.dbf
        17 /u01/app/oracle/oradata/xifenfei/xff/sysaux01.dbf
        18 /u01/app/oracle/oradata/xifenfei/xff/users01.dbf
        19 /u01/app/oracle/oradata/xifenfei/xff/example01.dbf

ORACLE 12C备份与恢复测试

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE 12C备份与恢复测试

12C引进了pdb的概念,使得rman的恢复相对来说复杂了一些,这里对pdb的常规备份和恢复进行了简单测试,供大家参考
cdb启动和pdb关系测试

[oracle@xifenfei tmp]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.0.2 Beta on Wed Dec 12 23:48:02 2012
Copyright (c) 1982, 2012, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup
ORACLE instance started.
Total System Global Area  939495424 bytes
Fixed Size                  2267128 bytes
Variable Size             662702088 bytes
Database Buffers          268435456 bytes
Redo Buffers                6090752 bytes
Database mounted.
Database opened.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4043918109 PDB$SEED                       READ ONLY
         3 2346805300 LX1                            MOUNTED
         4 2385557792 LX2                            MOUNTED
         5 1565384817 FF                             MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4043918109 PDB$SEED                       READ ONLY
         3 2346805300 LX1                            READ WRITE
         4 2385557792 LX2                            READ WRITE
         5 1565384817 FF                             READ WRITE

证明直接startup cdb里面的pdb不会自动open,需要手工进行open

rman使用cdb备份数据库

[oracle@xifenfei ~]$ rman target /
Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 21:36:08 2012
Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.
connected to target database: xifenfei (DBID=2412861330)
RMAN> backup filesperset = 5 as compressed backupset database format '/tmp/full_db_%U';
Starting backup at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/xifenfei/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/xifenfei/LX2/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/xifenfei/LX2/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_06nsn3uq_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00019 name=/u01/app/oracle/oradata/xifenfei/xffexample01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/xifenfei/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
input datafile file number=00018 name=/u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_07nsn407_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/xifenfei/pdbseed/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/xifenfei/pdbseed/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_08nsn41l_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/xifenfei/sysaux01.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/xifenfei/users01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/xifenfei/LX2/LX2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_09nsn440_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/full_db_0ansn45d_1_1 tag=TAG20121212T213626 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-DEC-12
RMAN> list backup summary;
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213250
2       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626
3       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626
4       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626
5       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626
6       B  F  A DISK        12-DEC-12       1       1       YES        TAG20121212T213626
RMAN> report schema;
Report of database schema for database with db_unique_name xifenfei
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               ***     /u01/app/oracle/oradata/xifenfei/system01.dbf
2    210      PDB$SEED:SYSTEM      ***     /u01/app/oracle/oradata/xifenfei/pdbseed/system01.dbf
3    550      SYSAUX               ***     /u01/app/oracle/oradata/xifenfei/sysaux01.dbf
4    165      PDB$SEED:SYSAUX      ***     /u01/app/oracle/oradata/xifenfei/pdbseed/sysaux01.dbf
5    310      UNDOTBS1             ***     /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
6    5        USERS                ***     /u01/app/oracle/oradata/xifenfei/users01.dbf
7    210      LX1:SYSTEM           ***     /u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
8    165      LX1:SYSAUX           ***     /u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
9    5        LX1:USERS            ***     /u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
10   210      LX2:SYSTEM           ***     /u01/app/oracle/oradata/xifenfei/LX2/system01.dbf
11   165      LX2:SYSAUX           ***     /u01/app/oracle/oradata/xifenfei/LX2/sysaux01.dbf
12   5        LX2:USERS            ***     /u01/app/oracle/oradata/xifenfei/LX2/LX2_users01.dbf
16   270      FF:SYSTEM            ***     /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
17   570      FF:SYSAUX            ***     /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
18   5        FF:USERS             ***     /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
19   341      FF:EXAMPLE           ***     /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    530      TEMP                 32767       /u01/app/oracle/oradata/xifenfei/temp01.dbf
2    20       PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/xifenfei/pdbseed/temp01.dbf
3    20       LX1:TEMP             32767       /u01/app/oracle/oradata/xifenfei/LX1/temp01.dbf
4    20       LX2:TEMP             32767       /u01/app/oracle/oradata/xifenfei/LX2/temp01.dbf
5    20       FF:TEMP              32767       /u01/app/oracle/oradata/xifenfei/xfftemp01.dbf

试验证明:通过rman通过cdb库的备份,可以实现对对应的cdb和所包含的pdb进行备份

配置pdb访问tns

[oracle@xifenfei ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.0.2        on 12-DEC-2012 22:33:27
Copyright (c) 1991, 2012, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.0.2
Start Date                12-DEC-2012 22:31:55
Uptime                    0 days 0 hr. 1 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/xifenfei/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=5500))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ff" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xifenfei" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "lx1" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "lx2" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
Service "xffXDB" has 1 instance(s).
  Instance "xff", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@xifenfei admin]$ vi tnsnames.ora
lx1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lx1)
    )
  )
ff =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ff)
    )
  )

sqlplus访问pdb

[oracle@xifenfei admin]$ sqlplus sys@lx1 as sysdba
SQL*Plus: Release 12.1.0.0.2 Beta on Wed Dec 12 22:35:07 2012
Copyright (c) 1982, 2012, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show con_name;
CON_NAME
------------------------------
LX1
SQL> create user xff identified by xifenfei;
User created.
SQL> GRANT SYSDBA TO XFF;
Grant succeeded.

rman备份pdb数据库

[oracle@xifenfei admin]$ rman target xff/xifenfei@lx1
Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 22:44:46 2012
Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.
connected to target database: xifenfei (DBID=2412861330)
RMAN> backup filesperset = 5 as compressed backupset database format '/tmp/lx1_db_%U';
Starting backup at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=256 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/lx1_db_0bnsn80f_1_1 tag=TAG20121212T224534 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 12-DEC-12
RMAN> report schema;
Report of database schema for database with db_unique_name xifenfei
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
7    210      LX1:SYSTEM           ***     /u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
8    165      LX1:SYSAUX           ***     /u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
9    5        LX1:USERS            ***     /u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
3    20       LX1:TEMP             32767       /u01/app/oracle/oradata/xifenfei/LX1/temp01.dbf

rman通过cdb备份pdb

[oracle@xifenfei admin]$ rman target /
Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 23:02:07 2012
Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.
connected to target database: xifenfei (DBID=2412861330)
RMAN> backup filesperset = 5 as compressed backupset pluggable database FF format '/tmp/ff_db_%U';
Starting backup at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=262 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
input datafile file number=00019 name=/u01/app/oracle/oradata/xifenfei/xffexample01.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
input datafile file number=00018 name=/u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-12
channel ORA_DISK_1: finished piece 1 at 12-DEC-12
piece handle=/tmp/ff_db_0cnsn8vm_1_1 tag=TAG20121212T230214 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
Finished backup at 12-DEC-12

模拟pdb库全库恢复

SQL> conn xff/xifenfei@lx1 as sysdba
Connected.
SQL> create table t_xifenfei as select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     19121
--在pdb中不能切换日志(因为日志是全局的)
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL> shutdown immediate;
Pluggable Database closed.
--删除数据文件
[oracle@xifenfei admin]$ rm /u01/app/oracle/oradata/xifenfei/LX1/*
--rman基于cdb恢复pdb库
[oracle@xifenfei ~]$ rman target /
Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 23:11:22 2012
Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.
connected to target database: xifenfei (DBID=2412861330)
RMAN> restore pluggable database lx1;
Starting restore at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/lx1_db_0bnsn80f_1_1
channel ORA_DISK_1: piece handle=/tmp/lx1_db_0bnsn80f_1_1 tag=TAG20121212T224534
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 12-DEC-12
RMAN> recover pluggable database lx1;
Starting recover at 12-DEC-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 12-DEC-12
RMAN> alter pluggable database lx1 open;
Statement processed
--验证恢复结果
SQL> conn xff/xifenfei@lx1 as sysdba
Connected.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     19121

试验证明:对于pdb库的备份,使用rman可以在cdb级别进行还原和恢复

模拟数据文件恢复

SQL> create table t_xifenfei tablespace example
  2   as
  3    select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     89604
SQL> col name for a60
SQL> set lines 134
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- -----------------------------------------------------------
         5 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
        16 /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
        17 /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
        18 /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
        19 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
--离线含测试数据的数据文件
SQL> alter database datafile 19 offline;
Database altered.
--删除数据文件
SQL> !rm /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
SQL> !ls -l /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
ls: /u01/app/oracle/oradata/xifenfei/xffexample01.dbf: No such file or directory
--尝试pdb级别恢复
[oracle@xifenfei ~]$ rman target sys/xifenfei@ff
Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 23:29:03 2012
Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.
connected to target database: xifenfei (DBID=2412861330)
RMAN> restore datafile 19;
Starting restore at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/12/2012 23:29:15
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 19 found to restore
--pdb级别不能识别对应数据文件(一种可能是我在cdb级别备份FF库,另一种可能bug)
RMAN> list backup of datafile 19;
specification does not match any backup in the repository
--在cdb级别还原
[oracle@xifenfei tmp]$ rman target /
Recovery Manager: Release 12.1.0.0.2        on Wed Dec 12 23:44:21 2012
Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.
connected to target database: xifenfei (DBID=2412861330)
RMAN> list backup of datafile 19;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    76.81M     DISK        00:00:44     12-DEC-12
        BP Key: 3   Status: AVAILABLE  Compressed: YES  Tag: TAG20121212T213626
        Piece Name: /tmp/full_db_07nsn407_1_1
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  19      Full 1860043    12-DEC-12 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    189.52M    DISK        00:01:25     12-DEC-12
        BP Key: 8   Status: AVAILABLE  Compressed: YES  Tag: TAG20121212T230214
        Piece Name: /tmp/ff_db_0cnsn8vm_1_1
  List of Datafiles in backup set 8
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  19      Full 1860043    12-DEC-12 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
RMAN> restore datafile 19;
Starting restore at 12-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00019 to /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/ff_db_0cnsn8vm_1_1
channel ORA_DISK_1: piece handle=/tmp/ff_db_0cnsn8vm_1_1 tag=TAG20121212T230214
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 12-DEC-12
--cdb级别恢复数据文件
RMAN> recover datafile 19;
Starting recover at 12-DEC-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 12-DEC-12
--cdb级别不能直接online
RMAN> alter database datafile 19 online;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 12/12/2012 23:46:15
ORA-01516: nonexistent log file, data file, or temporary file "19"
RMAN> alter pluggable database ff datafile 19 online;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 12/12/2012 23:47:53
ORA-65046: operation not allowed from outside a pluggable database
--进入pdb库进行online
SQL> alter database datafile 19 online;
Database altered.
--验证数据
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     89604

试验证明:rman通过cdb级别操作,还是一步步恢复了pdb中离线异常的数据文件

总结说明
1.rman可以比较好的操作cdb和pdb备份
2.pdb的备份和恢复可以通过cdb来完成

ORACLE 12C PDB部分功能测试

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE 12C PDB部分功能测试

ORACLE 12C中提出来CDB和PDB的概念,对于ORACLE的数据库来说,确实是一个新东西,他们可以分别理解为容器和插件(PDB插入在CDB中),CDB的管理和传统数据库区别不大,本篇文章对PDB的部分操作进行了简单说明(创建PDB,OPEN PDB,DROP PDB,Plug PDB,Unplugging PDB)
CREATE PDB

SQL> SELECT NAME,CDB FROM  V$DATABASE;
NAME      CDB
--------- ---
XIFENFEI  YES
SQL> select pdb_id,pdb_name,dbid,STATUS,CREATION_SCN  from dba_pdbs;
    PDB_ID PDB_NAME                             DBID STATUS        CREATION_SCN
---------- ------------------------------ ---------- ------------- ------------
         2 PDB$SEED                       4044122081 NORMAL             1661281
         3 FF                             1565322182 NORMAL             1720654
SQL> CREATE PLUGGABLE DATABASE xff_db ADMIN USER xff IDENTIFIED BY xifenfei
  2  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  3  DEFAULT TABLESPACE xifenfei
  4  DATAFILE '/u01/app/oracle/oradata/xifenfei/xff/xifenfei01.dbf' SIZE 25M AUTOEXTEND ON
  5  PATH_PREFIX = '/u01/app/oracle/oradata/xifenfei/xff/'
  6  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/xifenfei/pdbseed/',
  7  '/u01/app/oracle/oradata/xifenfei/xff/');
Pluggable database created.
SQL> select pdb_id,pdb_name,dbid,STATUS,CREATION_SCN  from dba_pdbs;
    PDB_ID PDB_NAME                             DBID STATUS        CREATION_SCN
---------- ------------------------------ ---------- ------------- ------------
         2 PDB$SEED                       4044122081 NORMAL             1661281
         3 FF                             1565322182 NORMAL             1720654
         4 XFF_DB                         2272981748 NEW                1771028

OPEN PDB

SQL> alter pluggable database xff_db open;
Pluggable database altered.
SQL> select pdb_id,pdb_name,dbid,STATUS,CREATION_SCN  from dba_pdbs;
    PDB_ID PDB_NAME                             DBID STATUS        CREATION_SCN
---------- ------------------------------ ---------- ------------- ------------
         2 PDB$SEED                       4044122081 NORMAL             1661281
         3 FF                             1565322182 NORMAL             1720654
         4 XFF_DB                         2272981748 NORMAL             1771028
SQL>  alter pluggable database all close immediate;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4044122081 PDB$SEED                       READ ONLY
         3 1565322182 FF                             MOUNTED
         4 2272981748 XFF_DB                         MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4044122081 PDB$SEED                       READ ONLY
         3 1565322182 FF                             READ WRITE
         4 2272981748 XFF_DB                         READ WRITE

Unplugging a PDB from a CDB

SQL> alter pluggable database FF close immediate;
Pluggable database altered.
SQL> alter  pluggable database ff UNPLUG into '/tmp/ff.xml';
Pluggable database altered.

DROP PDB

SQL> DROP PLUGGABLE DATABASE xff_db INCLUDING DATAFILES;
Pluggable database dropped.
SQL> DROP PLUGGABLE DATABASE ff INCLUDING DATAFILES;
Pluggable database dropped.

Plug Unplugged PDB into CDB

SQL> SELECT NAME,CDB FROM  V$DATABASE;
NAME      CDB
--------- ---
XFF_L     YES
SQL> create pluggable database ff using '/tmp/ff.xml'
  2  copy file_name_convert=('/u01/app/oracle/oradata/xifenfei/FF/','/u01/app/oracle/oradata/xff_l/xff');
Pluggable database created.
SQL> exec DBMS_PDB.SYNC_PDB();
PL/SQL procedure successfully completed.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4043918109 PDB$SEED                       READ ONLY
         3 2346805300 LX1                            MOUNTED
         4 2385557792 LX2                            MOUNTED
         5 1565384817 FF                             MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4043918109 PDB$SEED                       READ ONLY
         3 2346805300 LX1                            READ WRITE
         4 2385557792 LX2                            READ WRITE
         5 1565384817 FF                             READ WRITE
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/xff_l/system01.dbf
/u01/app/oracle/oradata/xff_l/pdbseed/system01.dbf
/u01/app/oracle/oradata/xff_l/sysaux01.dbf
/u01/app/oracle/oradata/xff_l/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/xff_l/undotbs01.dbf
/u01/app/oracle/oradata/xff_l/users01.dbf
/u01/app/oracle/oradata/xff_l/LX1/system01.dbf
/u01/app/oracle/oradata/xff_l/LX1/sysaux01.dbf
/u01/app/oracle/oradata/xff_l/LX1/LX1_users01.dbf
/u01/app/oracle/oradata/xff_l/LX2/system01.dbf
/u01/app/oracle/oradata/xff_l/LX2/sysaux01.dbf
/u01/app/oracle/oradata/xff_l/LX2/LX2_users01.dbf
/u01/app/oracle/oradata/xff_l/xffsystem01.dbf
/u01/app/oracle/oradata/xff_l/xffsysaux01.dbf
/u01/app/oracle/oradata/xff_l/xffSAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/xff_l/xffexample01.dbf
16 rows selected.

补充说明:本部分内容比较多,本篇blog,只是对其中的很小一部分进行了测试,确实证明可以对pdb实现在不同的cdb中实现迁移,对于该项操作,可以在dbca和gc中实现相同操作.由于12c数据库尚未正式发布,该部分功能只是出于个人测试目的.

ORACLE 12C安装预览

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE 12C安装预览

关注中的ORACLE 12C今天装了下，发现几点不同之处截图如下

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0
SQL> set pages 150
SQL> col name for a60
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- ------------------------------------------------------------
         1 /u01/app/oracle/oradata/xifenfei/system01.dbf
         2 /u01/app/oracle/oradata/xifenfei/pdbseed/system01.dbf
         3 /u01/app/oracle/oradata/xifenfei/sysaux01.dbf
         4 /u01/app/oracle/oradata/xifenfei/pdbseed/sysaux01.dbf
         5 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
         6 /u01/app/oracle/oradata/xifenfei/users01.dbf
         7 /u01/app/oracle/oradata/xifenfei/LX1/system01.dbf
         8 /u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf
         9 /u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf
        10 /u01/app/oracle/oradata/xifenfei/LX2/system01.dbf
        11 /u01/app/oracle/oradata/xifenfei/LX2/sysaux01.dbf
        12 /u01/app/oracle/oradata/xifenfei/LX2/LX2_users01.dbf
12 rows selected.

标签归档：ORACLE 12C