Category Archives: 数据库

ORACLE 12C PDB部分功能测试

Posted on by

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

Posted on by

关注中的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$对象相关处理

Posted on by

在日常的数据库维护中，经常出现因为数据库登录审计的功能启动,导致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错误处理

Posted on by

数据库版本(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]故障解决

Posted on by

朋友一数据库因为断电,被重建控制文件等操作折腾的最后出现在启动的时候出现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来检测是否有异常事务:如果有重要基表对象异常,需要重建库;如果是个别其他对象异常,可以通过重建该对象解决