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

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测试

通过一段时间的宣传,很多人都知道了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备份与恢复测试

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来完成

EXADATA与非EXADATA搭建DATAGURAD关于EHCC特性测试

随着xd的越来越普及，不少的企业使用了xd，但是不少企业因为资金有限，只有一台xd，但是为了实现数据的容灾，可能会使用一台非xd的机器来通过dataguard来实现容灾,但是因为xd的ehcc新特性,官方宣传是只在xd中支持,如果dg的备库不是xd。那么会怎么样,这里通过测试得出如下一些结论：xd与非xd可以构造dg,ehcc功能在xd上无法高效使用。对于这样的环境条件下,使用ORACLE自带压缩效率更高.针对ehcc压缩效率很低,个人猜测,是因为xd检查到备库是非xd环境,直接对ehcc进行了降级压缩处理,从而出现了ehcc的压缩效率比oltp还低(牺牲了xd的性能,确保了数据的安全,看来xd的设计还是考虑的比较全面)
xd基本信息

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> show parameter clu;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string
SQL> SELECT OPEN_MODE, DATABASE_ROLE ,NAME FROM V$DATABASE;
OPEN_MODE            DATABASE_ROLE    NAME
-------------------- ---------------- ---------
READ WRITE           PRIMARY          xxxxxx
SQL> !uname -a
Linux dm01db02 2.6.18-194.3.1.0.4.el5 #1 SMP Sat Feb 19 03:38:37 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

xd创建模拟表

SQL> create table t_FF_c compress as select  * from dba_objects;
Table created.
SQL> create table t_FF_c_o compress for oltp  as select  * from dba_objects;
Table created.
SQL> create table t_FF_q_l compress for query low as select  * from dba_objects;
Table created.
SQL> create table t_FF_q_h compress for query high  as select  * from dba_objects;
Table created.
SQL> create table t_FF_a_l compress for archive low  as select  * from dba_objects;
Table created.
SQL> create table t_FF_a_h compress for archive high as select * from dba_objects;
Table created.
SQL> create table t_ff as select * from dba_objects;
Table created.

xd查询模拟表

SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%';  2    3
OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   11
SYS                            T_FF_A_H                               10 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC

通过这里发现,带有非dg的xd使用ehcc压缩效率都低了很多

非xd备库基本信息

SQL> SELECT OPEN_MODE, DATABASE_ROLE ,NAME FROM V$DATABASE;
OPEN_MODE            DATABASE_ROLE    NAME
-------------------- ---------------- ---------
READ ONLY WITH APPLY PHYSICAL STANDBY xxxxxx
SQL> show parameter clu;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string
SQL> !uname -a
Linux oradg 2.6.18-238.el5xen #1 SMP Sun Dec 19 14:42:02 EST 2010 x86_64 x86_64 x86_64 GNU/Linux

查询非xd dg备库

SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%';
OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   11
SYS                            T_FF_A_H                               10 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC
SQL> SELECT COUNT(*) FROM T_FF_Q_L;
  COUNT(*)
----------
     94709
SQL> SELECT COUNT(*) FROM T_FF_Q_H;
  COUNT(*)
----------
     94710
SQL> SELECT COUNT(*) FROM T_FF_C_O;
  COUNT(*)
----------
     94708
SQL> SELECT COUNT(*) FROM T_FF_C;
  COUNT(*)
----------
     94707
SQL> SELECT COUNT(*) FROM T_FF_A_L;
  COUNT(*)
----------
     94711
SQL> SELECT COUNT(*) FROM T_FF_A_H;
  COUNT(*)
----------
     94712
SQL> select count(*) from t_FF;
  COUNT(*)
----------
    94713

通过这里测试证明,对于非xd dg库,可以正常的查询xd上的ehcc相关表,而且相关大小也相同(物理dg当然相同了)

测试xd与非xd dg测试ehcc的dml操作

--xd 主库
SQL> update t_ff_a_h set owner='www.xifenfei.com';
94712 rows updated.
SQL> commit;
Commit complete.
SQL>  select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%';  2    3
OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   11
SYS                            T_FF_A_H                               11 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC
7 rows selected.
SQL> alter system switch logfile;
System altered.
--非xd 备库
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%';
OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   11
SYS                            T_FF_A_H                               11 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC

证明对于xd与非xd构成的dg环境,可以执行dml操作.

测试xd与非xd dg的ehcc的append操作

--xd准备三张测试空表
SQL> create table t_FF_a_l_1 compress for archive low as select * from t_FF  where 1=0;
Table created.
SQL> create table t_FF_a_h_1 compress for archive high  as select * from t_FF  where 1=0;
Table created.
SQL>truncate table t_FF;
Table truncated.
--插入数据(每个表执行5次)
SQL> insert /*+ APPEND */ into t_FF_a_l_1 select * from dba_objects;
94714 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into t_FF_h_l_1 select * from dba_objects;
94714 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into t_FF select * from dba_objects;
94714 rows created.
SQL> commit;
Commit complete.
--查看相关表数据量
SQL> select count(*) from t_FF_a_l_1;
  COUNT(*)
----------
    473570
SQL> select count(*) from t_FF_a_h_1;
  COUNT(*)
----------
    473570
SQL> select count(*) from t_FF;
  COUNT(*)
----------
    473570
--查看xd主库
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%';
OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   52
SYS                            T_FF_A_H                               11 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC
SYS                            T_FF_A_H_1                             47 ARCHIVE HIGH
SYS                            T_FF_A_L_1                             47 ARCHIVE LOW
--查看非xd备库
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name  and t.table_name like 'T_FF%';
OWNER                          SEGMENT_NAME                       T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS                            T_FF                                   52
SYS                            T_FF_A_H                               11 ARCHIVE HIGH
SYS                            T_FF_A_L                               10 ARCHIVE LOW
SYS                            T_FF_Q_H                               10 QUERY HIGH
SYS                            T_FF_Q_L                               10 QUERY LOW
SYS                            T_FF_C_O                                4 OLTP
SYS                            T_FF_C                                  4 BASIC
SYS                            T_FF_A_H_1                             47 ARCHIVE HIGH
SYS                            T_FF_A_L_1                             47 ARCHIVE LOW

试验整体结论
1.xd可以与非xd机器构建dg容灾环境(不会因为非dg不支持ehcc而导致无法搭建他们之间的dg)
2.xd与非xd的dg,ehcc功能大大缩水,基本上和非压缩状态差不多,比OLTP低很多
3.xd与非xd的dg在备库中支持select,dml,hint append等操作,这些操作是因为ehcc表在xd端就进行了ehcc降级导致

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安装预览

关注中的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.

关于aud$对象相关处理

在日常的数据库维护中，经常出现因为数据库登录审计的功能启动,导致system表空间被用满.从而出现异常,一般建议把aud$相关对象迁移到其他表空间,从而避免system被用完的风险.
人工move aud$相关对象

alter table AUDIT$ move tablespace users;
alter table AUDIT_ACTIONS move tablespace users;
alter table AUD$ move tablespace users;
alter table AUD$ move lob(SQLBIND) store as SYS_IL0000000384C00041$$ (tablespace users);
alter table AUD$ move lob(SQLTEXT) store as SYS_IL0000000384C00041$$ (tablespace users);
alter index I_AUDIT rebuild online tablespace users;
alter index I_AUDIT_ACTIONS rebuild online tablespace users;
--可能修改值(index和lob index)
SQL> select COLUMN_NAME,index_name from dba_lobs where owner='SYS' and table_name='AUD$';
COLUMN_NAME                              INDEX_NAME
---------------------------------------- ------------------------------
SQLBIND                                  SYS_IL0000000384C00040$$
SQLTEXT                                  SYS_IL0000000384C00041$$
SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUD$';
INDEX_NAME
------------------------------
SYS_IL0000000384C00040$$
SYS_IL0000000384C00041$$
SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT$';
INDEX_NAME
------------------------------
I_AUDIT
SQL>  SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT_ACTIONS';
INDEX_NAME
------------------------------
I_AUDIT_ACTIONS

DBMS_AUDIT_MGMT实现迁移

conn / as sysdba
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'USERS');
END;
/

验证DBMS_AUDIT_MGMT效果

SQL> select segment_name,tablespace_name from dba_segments where
  2  segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',
  3  'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');
SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
AUDIT_ACTIONS                                                                     SYSTEM
AUDIT$                                                                            SYSTEM
AUD$                                                                              SYSTEM
SYS_IL0000000384C00041$$                                                          SYSTEM
SYS_IL0000000384C00040$$                                                          SYSTEM
I_AUDIT_ACTIONS                                                                   SYSTEM
I_AUDIT                                                                           SYSTEM
SQL> BEGIN
  2  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
  3  audit_trail_location_value => 'USERS');
  4  END;
  5  /
PL/SQL procedure successfully completed.
SQL> select segment_name,tablespace_name from dba_segments where
  2   segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',
  3   'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');
SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
AUDIT_ACTIONS                                                                     SYSTEM
AUDIT$                                                                            SYSTEM
AUD$                                                                              USERS
SYS_IL0000000384C00041$$                                                          USERS
SYS_IL0000000384C00040$$                                                          USERS
I_AUDIT_ACTIONS                                                                   SYSTEM
I_AUDIT                                                                           SYSTEM

通过试验证明DBMS_AUDIT_MGMT就是迁移了AUD$表中相关对象,对于和审计相关的其他几个对象并未迁移到其他表空间

aud$相关说明
1.DBMS_AUDIT_MGMT版本支持情况

It is still not supported (but it works) to use it on 10.2.0.4 and 11.1.0.7 for non-Audit Vault Environment.
Using RDBMS with Audit Vault, it is supported  for 10.2.0.4.0 and 11.1.0.7.0
as the DBMS_AUDIT_MGMT Package is provided to be used with an Audit Vault Environment.

2.该包可以实现在线迁移，特别是在高业务的系统中,可以实现在线迁移,而人工的move操作不能实现在线处理
3.对于AUD$对象，如果登录审计数据不是非常重要,可以通过truncate来解决一时的问题，在业务高的系统，可能truncate不能马上操作成功，可以尝试使用11gr2的新特性alter session set ddl_lock_timeout = 10;来实现自动ddl尝试
4.如果确定不需要登录审计功能，可以通过设置audit_trail=none来关闭(需要重启实例)

delete expired backup报ORA-19606错误处理

数据库版本(exadata x2 1/4)

SQL> select *from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

使用delete expired backup报ORA-19606错

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_4 channel at 12/05/2012 13:35:07
ORA-19606: Cannot copy or restore to snapshot control file

报错原因

The snapshot controlfile is cataloged as a controlfile coy and is now obsolete.
RMAN cannot delete this file as it is used by rman.

处理方法[ID 1215493.1]

Since RMAN will continue to use that file as it's snapshot copy,
you must change the location/name that RMAN is using before it will allow you to delete the file.
1. Set new name (or location) for RMAN to use for snapshot controlfile:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCF_temp.ORA';
2. Remove the snapshot controlfile from the RMAN information as a controlfile copy.
delete 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA' on operating system.
3. Crosscheck and delete the file from RMAN:
crosscheck controlfilecopy 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
delete expired controlfilecopy 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
4. Set the snapshot controlfile name (or location) to original:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
5. Or to set the snapshot controlfile name back to default value:
CONFIGURE SNAPSHOT CONTROLFILE NAME clear;

处理方法就是把SNAPSHOT CONTROLFILE的配置路径改变,然后删除控制文件快照

ORA-00600[kcfrbd_3]故障解决

朋友一数据库因为断电,被重建控制文件等操作折腾的最后出现在启动的时候出现ORA-00600[kcfrbd_3]

Wed Dec 05 10:26:34 2012
Thread 1 advanced to log sequence 11
Thread 1 opened at log sequence 11
  Current log# 1 seq# 11 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
Wed Dec 05 10:26:34 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Dec 05 10:26:34 2012
SMON: enabling cache recovery
Wed Dec 05 10:26:35 2012
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Wed Dec 05 10:26:35 2012
SMON: enabling tx recovery
Wed Dec 05 10:26:35 2012
Database Characterset is ZHS16GBK
Wed Dec 05 10:26:35 2012
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_548.trc:
ORA-00600: internal error code, arguments: [kcfrbd_3], [2], [2279045], [1], [2277120], [2277120], [], []
replication_dependency_tracking turned off (no async multimaster replication found)
Wed Dec 05 10:26:36 2012
Fatal internal error happened while SMON was doing active transaction recovery.
Wed Dec 05 10:26:36 2012
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_548.trc:
ORA-00600: internal error code, arguments: [kcfrbd_3], [2], [2279045], [1], [2277120], [2277120], [], []
SMON: terminating instance due to error 474

这个错误很明显:数据库已经open成功了，但是因为有事务不能正常被回滚，然后数据库的smon进程异常，从而使得数据库不能正常启动，解决该问题的方法也是很简单，就是常规的undo处理思路(使用人工undo管理,event屏蔽事务,隐含参数屏蔽回滚段)，然后重建undo表空间，这个时候可以结合txchecker来检测是否有异常事务:如果有重要基表对象异常,需要重建库;如果是个别其他对象异常,可以通过重建该对象解决