ORACLE 12C RMAN 功能增强

在ORACLE 12C中对rman的功能有了不少增强,在以前的文章中写过RMAN RECOVER TABLE功能,这里另外补充rman增强的两个小功能(sql语句和数据文件分割)
数据库版本

SQL>  select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

rman对sql语句支持增强

[oracle@xifenfei tmp]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Sat Jun 1 14:07:50 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB (DBID=1922813718)

RMAN> select sysdate from dual;

using target database control file instead of recovery catalog
SYSDATE  
---------
01-JUN-13

RMAN> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Statement processed

RMAN>  select sysdate from dual;

SYSDATE            
-------------------
2013-06-01 14:16:48

RMAN> desc v$log

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP#                                             NUMBER                      
 THREAD#                                            NUMBER                      
 SEQUENCE#                                          NUMBER                      
 BYTES                                              NUMBER                      
 BLOCKSIZE                                          NUMBER                      
 MEMBERS                                            NUMBER                      
 ARCHIVED                                           VARCHAR2(3)                 
 STATUS                                             VARCHAR2(16)                
 FIRST_CHANGE#                                      NUMBER                      
 FIRST_TIME                                         DATE                        
 NEXT_CHANGE#                                       NUMBER                      
 NEXT_TIME                                          DATE                        
 CON_ID                                             NUMBER                      

这里看到rman只是sql语句中的select和desc用法

rman分割数据文件增强

RMAN>  CONFIGURE DEVICE TYPE DISK PARALLELISM 3;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> backup incremental level 1 section size 30M datafile 1 format '/tmp/system_%U.rman';

Starting backup at 01-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=269 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=24 device type=DISK
no parent backup or copy of datafile 1 found
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/cdb/system01.dbf
backing up blocks 1 through 3840
channel ORA_DISK_1: starting piece 1 at 01-JUN-13
channel ORA_DISK_2: starting incremental level 1 datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/cdb/system01.dbf
……………………
backing up blocks 96001 through 99840
channel ORA_DISK_3: starting piece 26 at 01-JUN-13
channel ORA_DISK_1: finished piece 24 at 01-JUN-13
piece handle=/tmp/system_02ob3pg1_24_1.rman tag=TAG20130601T144518 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/cdb/system01.dbf
backing up blocks 99841 through 101120
channel ORA_DISK_1: starting piece 27 at 01-JUN-13
channel ORA_DISK_2: finished piece 25 at 01-JUN-13
piece handle=/tmp/system_02ob3pg1_25_1.rman tag=TAG20130601T144518 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_3: finished piece 26 at 01-JUN-13
piece handle=/tmp/system_02ob3pg1_26_1.rman tag=TAG20130601T144518 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:06
channel ORA_DISK_1: finished piece 27 at 01-JUN-13
piece handle=/tmp/system_02ob3pg1_27_1.rman tag=TAG20130601T144518 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 01-JUN-13

备份文件情况

[oracle@xifenfei tmp]$ ll -ltr system*
-rw-r----- 1 oracle dba 14761984 Jun  1 14:45 system_02ob3pg1_1_1.rman
-rw-r----- 1 oracle dba  9535488 Jun  1 14:45 system_02ob3pg1_2_1.rman
-rw-r----- 1 oracle dba 16973824 Jun  1 14:45 system_02ob3pg1_4_1.rman
-rw-r----- 1 oracle dba 18284544 Jun  1 14:45 system_02ob3pg1_3_1.rman
-rw-r----- 1 oracle dba 12804096 Jun  1 14:45 system_02ob3pg1_5_1.rman
-rw-r----- 1 oracle dba 29163520 Jun  1 14:45 system_02ob3pg1_6_1.rman
-rw-r----- 1 oracle dba 31326208 Jun  1 14:46 system_02ob3pg1_7_1.rman
-rw-r----- 1 oracle dba 30851072 Jun  1 14:46 system_02ob3pg1_8_1.rman
-rw-r----- 1 oracle dba 30801920 Jun  1 14:46 system_02ob3pg1_9_1.rman
-rw-r----- 1 oracle dba 23977984 Jun  1 14:46 system_02ob3pg1_11_1.rman
-rw-r----- 1 oracle dba 28508160 Jun  1 14:46 system_02ob3pg1_10_1.rman
-rw-r----- 1 oracle dba 30277632 Jun  1 14:46 system_02ob3pg1_12_1.rman
-rw-r----- 1 oracle dba 31498240 Jun  1 14:46 system_02ob3pg1_13_1.rman
-rw-r----- 1 oracle dba 31498240 Jun  1 14:47 system_02ob3pg1_14_1.rman
-rw-r----- 1 oracle dba 31498240 Jun  1 14:47 system_02ob3pg1_15_1.rman
-rw-r----- 1 oracle dba 30507008 Jun  1 14:47 system_02ob3pg1_17_1.rman
-rw-r----- 1 oracle dba 30834688 Jun  1 14:47 system_02ob3pg1_16_1.rman
-rw-r----- 1 oracle dba 31498240 Jun  1 14:47 system_02ob3pg1_18_1.rman
-rw-r----- 1 oracle dba 30244864 Jun  1 14:47 system_02ob3pg1_19_1.rman
-rw-r----- 1 oracle dba 29016064 Jun  1 14:47 system_02ob3pg1_20_1.rman
-rw-r----- 1 oracle dba 29212672 Jun  1 14:47 system_02ob3pg1_21_1.rman
-rw-r----- 1 oracle dba 30728192 Jun  1 14:47 system_02ob3pg1_22_1.rman
-rw-r----- 1 oracle dba 29384704 Jun  1 14:47 system_02ob3pg1_23_1.rman
-rw-r----- 1 oracle dba 26566656 Jun  1 14:47 system_02ob3pg1_24_1.rman
-rw-r----- 1 oracle dba 24928256 Jun  1 14:48 system_02ob3pg1_25_1.rman
-rw-r----- 1 oracle dba 19324928 Jun  1 14:48 system_02ob3pg1_26_1.rman
-rw-r----- 1 oracle dba  6291456 Jun  1 14:48 system_02ob3pg1_27_1.rman

在12C之前的版本,ORACLE 11GR2只是对于全备的备份集备份(非增量,非copy备份方式)方式支持数据文件分割备份功能,对于11.2之前的版本均不支持该功能.在12C中rman可以支持对于全备,增量备份,copy备份全部支持分割数据文件备份(CONTROLFILE,SPFILE不支持)

root 用户操作 ORACLE 数据库导致悲剧

接到同事请求,说客户的linux redhat 5.8平台部署的11.2.0.3 RAC 节点2挂掉了,报磁盘IO异常,数据库hang住

Fri Jun 14 12:01:22 2013
Thread 2 advanced to log sequence 369 (LGWR switch)
  Current log# 49 seq# 369 mem# 0: +DATA/q9db/onlinelog/group_49.861.817830099
Fri Jun 14 12:01:22 2013
Archived Log entry 89300 added for thread 2 sequence 368 ID 0x35324053 dest 1:
Fri Jun 14 14:26:18 2013
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_11788.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_11788.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
WARNING: failed to read mirror side 1 of virtual extent 441 logical extent 0 of file 625 
  in group [2.3857217523] from disk DATA_0001
  allocation unit 377890 reason error; if possible, will try another mirror side
Fri Jun 14 14:31:17 2013
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_13767.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_13767.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
WARNING: failed to read mirror side 1 of virtual extent 441 logical extent 0 of file 625 
  in group [2.3857217523] from disk DATA_0001
  allocation unit 377890 reason error; if possible, will try another mirror side

在12点钟数据库运行正常,无任何错误,突然到了14多出现ORA-15025/ORA-27041,并且重启ORACLE 数据库恢复正常。该错误很明显是数据库无权限访问ASM DISK,检查ASM实例日志

Thu Jun 13 19:01:21 2013
ASMB started with pid=25, OS id=25066 
Thu Jun 13 19:01:22 2013
NOTE: client +ASM2:+ASM registered, osid 25068, mbr 0x0
WARNING: failed to online diskgroup resource ora.DATA.dg (unable to communicate with CRSD/OHASD)
Thu Jun 13 19:01:24 2013
WARNING: failed to online diskgroup resource ora.OCR_VOTE.dg (unable to communicate with CRSD/OHASD)
Thu Jun 13 19:01:57 2013
NOTE: client q9db2:q9db registered, osid 25732, mbr 0x1
Thu Jun 13 19:02:31 2013
ALTER SYSTEM SET local_listener=' (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.8.8.33)
 (PORT=1521))))' SCOPE=MEMORY SID='+ASM2';
Fri Jun 14 14:53:09 2013
SQL> ALTER DISKGROUP OCR_VOTE DISMOUNT  /* asm agent *//* {2:61929:97} */ 
Fri Jun 14 14:53:10 2013
SQL> ALTER DISKGROUP ARCH DISMOUNT  /* asm agent *//* {2:61929:97} */ 
Fri Jun 14 14:53:10 2013
SQL> ALTER DISKGROUP DATA DISMOUNT  /* asm agent *//* {2:61929:97} */ 

这里可以明显的看到,ASM实例在该时间点无任何错误,证明一切运行正常,查看系统日志,在该故障点,message中无任何记录,查看asm disk权限

[oracle@q9db02 trace]$ ll /dev/mapper/
total 0
crw------- 1 root root    10, 60 Jun  9 11:08 control
brw-rw---- 1 grid asmdba 253, 15 Jun 14 16:20 q9datalun1
brw-rw---- 1 grid asmdba 253, 16 Jun 14 16:20 q9datalun2
brw-rw---- 1 grid asmdba 253, 17 Jun 14 16:20 q9datalun3
brw-rw---- 1 grid asmdba 253, 18 Jun 14 16:19 q9datalun4
brw-rw---- 1 grid asmdba 253, 19 Jun 14 16:20 q9datalun5
brw-rw---- 1 grid asmdba 253, 20 Jun 14 16:20 q9datalun6
brw-rw---- 1 grid asmdba 253, 21 Jun 14 16:19 q9datalun7
brw-rw---- 1 grid asmdba 253,  4 Jun 14 16:20 q9datalun8
brw-rw---- 1 grid asmdba 253,  5 Jun 14 16:20 q9votelun1

所有文件权限没有任何问题,和当初部署之时完全相同而且运行了一段时间都正常,部署之时权限

[oracle@q9db02 trace]$ more /etc/rc.local
chown grid:asmdba /dev/mapper/q9votelun1
chmod 660 /dev/mapper/q9votelun1
chown grid:asmdba /dev/mapper/q9datalun1
chmod 660 /dev/mapper/q9datalun1
chown grid:asmdba /dev/mapper/q9datalun2
chmod 660 /dev/mapper/q9datalun2
chown grid:asmdba /dev/mapper/q9datalun3
chmod 660 /dev/mapper/q9datalun3
chown grid:asmdba /dev/mapper/q9datalun4
chmod 660 /dev/mapper/q9datalun4
chown grid:asmdba /dev/mapper/q9datalun5
chmod 660 /dev/mapper/q9datalun5
chown grid:asmdba /dev/mapper/q9datalun6
chmod 660 /dev/mapper/q9datalun6
chown grid:asmdba /dev/mapper/q9datalun7
chmod 660 /dev/mapper/q9datalun7
chown grid:asmdba /dev/mapper/q9datalun8
chmod 660 /dev/mapper/q9datalun8
chown grid:asmdba /dev/mapper/q9datalun8
chmod 660 /dev/mapper/q9datalun8

因为这里权限没有任何改变,而且asm disk权限正确,系统日志无任何日志,证明该问题不是因为ASM DISK权限改变导致,那我怀疑是人做了不该做的操作,比喻临时性修改了ASM DISK权限,然后有修改回来了,或者是不正常的用户操作了数据库,而这些操作更加可能是root用户操作,分析root用户操作记录

--history部分记录
  803  su  oracle
  804  exit
  805  cd /tmp
  806  ls
  807  cd sysbench/
  808  cd bin/
  809  ls
  810  ORACLE_SID=q9db2
  811  export ORACLE_BASE
  812  export ORACLE_HOME
  813  ./sysbench --test=oltp --oltp-table-name=sysbench --oltp-table-size=1 --oracle-db=Q9DB 
       --oracle-user=sysbench --oracle-password=sysbench --db-driver=oracle  prepare
  814  syssql
  815  sqlplus system/sysbench@q9db02
  816  sqlplus system/q9db@q9db02
  817  echo $ORACLE_HOME
  818  cd $ORACLE_HOME/network/
  819  vi admin/tnsnames.ora 
  820  sqlplus system/NEWQ9DB
  821   echo $ORACLE_HOME
  822  vi ~/.bash_profile 
  823   echo $ORACLE_SID
  824  ps -ef | grep smon
  825  sqlplus system/NEWQ9DB
  826  exit

这里很明显的看到,由于SA想使用sysbench做系统基线测试,使用了root用户登录数据库并进行了相关操作,从而出现了该问题,因为ASM DISK 所有者是grid:asmdba,权限是660,root用户无法对ASM DISK进行读写操作,从而出现了上述错误。让同事协助SA重现上述操作,果然出现完全相同的错误,而且退出root session,数据库恢复正常

Fri Jun 14 15:44:24 2013
Archived Log entry 89330 added for thread 2 sequence 389 ID 0x35324053 dest 1:
Fri Jun 14 15:50:42 2013
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_29404.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_29404.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
WARNING: failed to read mirror side 1 of virtual extent 473 logical extent 0 of file 625 
  in group [2.3857045540] from disk DATA_0001
  allocation unit 377894 reason error; if possible, will try another mirror side
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_29404.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun4"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 1: '+DATA/q9db/datafile/system.625.817825255'
ORA-15081: failed to submit an I/O operation to a disk
Errors in file /u01/app/oracle/diag/rdbms/q9db/q9db2/trace/q9db2_ora_29404.trc:
ORA-15025: could not open disk "/dev/mapper/q9datalun4"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 1: '+DATA/q9db/datafile/system.625.817825255'
ORA-15081: failed to submit an I/O operation to a disk
WARNING: failed to read mirror side 1 of virtual extent 652 logical extent 0 of file 625 
  in group [2.3857045540] from disk DATA_0003
  allocation unit 377939 reason error; if possible, will try another mirror side
Fri Jun 14 15:55:58 2013
Thread 2 advanced to log sequence 391 (LGWR switch)
  Current log# 41 seq# 391 mem# 0: +DATA/q9db/onlinelog/group_41.853.817830085
Fri Jun 14 15:55:58 2013
Archived Log entry 89331 added for thread 2 sequence 390 ID 0x35324053 dest 1:
Thread 2 advanced to log sequence 392 (LGWR switch)
  Current log# 42 seq# 392 mem# 0: +DATA/q9db/onlinelog/group_42.854.817830087

在ASM ORACLE RAC环境中,使用root操作oracle 数据库导致该错误,强烈建议:操作oracle数据库,请使用oracle数据库安装用户(最少也是同一个所属组用户)运行,超级用户root对于oracle来说也不是万能的

ORACLE 12C CDB中PDB参数管理机制

在ORACLE 12C中参数文件只是记录了cdb的参数信息,没有记录任何的pdb的信息,那ORACLE是如何管理使得各个pdb有自己的参数,这里通过试验的出来ORACLE 12C CDB环境中是通过参数文件结合PDB_SPFILE$来实现参数管理
数据库版本

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

pdb信息

SQL>  select PDB_NAME,CON_UID,pdb_id,status from dba_pdbs;

PDB_NAME      CON_UID     PDB_ID STATUS
---------- ---------- ---------- -------------
PDB1       3313918585          3 NORMAL
PDB$SEED   4048821679          2 NORMAL
PDB2       3872456618          4 NORMAL

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4048821679 PDB$SEED                       READ ONLY
         3 3313918585 PDB1                           READ WRITE
         4 3872456618 PDB2                           MOUNTED

CDB$ROOT中修改参数

--指定container=all
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> alter system set open_cursors=500 container=all;

System altered.

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     500

SQL> alter session set container=pdb1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     500


--在CDB$ROOT中修改不指定container参数表示全部pdb生效
SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> alter system set open_cursors=100;

System altered.

SQL>  show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100

SQL> alter session set container=pdb1;

Session altered.

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100 

--指定container=current
SQL> alter system set open_cursors=120 container=current;

System altered.

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     120
SQL> alter session set container=pdb2 ;

Session altered.

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     120

这里可以看出来,在ROOT中修改参数,默认情况和指定container=all/current均是所有open的pdb都生效.
这里有个疑问ORACLE的参数文件只是记录的cdb的sid的参数,并未记录各个pdb的参数,那是如何实现cdb中各个pdb参数不一致的呢?继续分析

修改pdb参数做10046

SQL> show con_name;

CON_NAME
------------------------------
PDB1

SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Statement processed.
SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_18377.trc
SQL> alter system set sessions=100;

System altered.

SQL> oradebug EVENT 10046 trace name context off
Statement processed.

--继续修改pdb参数
SQL> alter session set container=pdb1;

Session altered.

SQL>  oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Statement processed.
SQL>  oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_20275.trc
SQL> alter system set sessions=101;

System altered.

SQL> oradebug EVENT 10046 trace name context off
Statement processed.

分析trace文件

--第一次修改pdb参数值
insert into pdb_spfile$(db_uniq_name, pdb_uid, sid, name, value$, comment$)  values(:1,:2,:3,:4,:5,:6)
END OF STMT
PARSE #140085118752824:c=3999,e=3397,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=99767937623
BINDS #140085118752824:
 Bind#0
  oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=7fffcfaa5842  bln=32  avl=03  flg=09
  value="cdb"
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f681bbb2170  bln=22  avl=06  flg=05
  value=3313918585
 Bind#2
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=7fffcfaa46f8  bln=32  avl=01  flg=09
  value="*"
 Bind#3
  oacdty=01 mxl=32(08) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=0bc220d8  bln=32  avl=08  flg=09
  value="sessions"
 Bind#4
  oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=7fffcfaa474c  bln=32  avl=03  flg=09
  value="100"
 Bind#5
  oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=00000000  bln=32  avl=00  flg=09

--第二次修改pdb参数值(相同参数)
update pdb_spfile$ set value$=:5, comment$=:6  where name=:1 and pdb_uid=:2 and db_uniq_name=:3 and sid=:4
BINDS #140603847818408:
 Bind#0
  oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=7ffff6477dcc  bln=32  avl=03  flg=09
  value="101"
 Bind#1
  oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=00000000  bln=32  avl=00  flg=09
 Bind#2
  oacdty=01 mxl=32(08) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=0bc220d8  bln=32  avl=08  flg=09
  value="sessions"
 Bind#3
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fe0e2638320  bln=22  avl=06  flg=05
  value=3313918585
 Bind#4
  oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=7ffff6478ec2  bln=32  avl=03  flg=09
  value="cdb"
 Bind#5
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=7ffff6477d78  bln=32  avl=01  flg=09
  value="*"

通过这里我们发现在独立修改pdb参数之时,其本质是在pdb_spfile$基表中插入或者修改相关记录(第一次修改插入,后续修改是更新)

关于pdb_spfile$基表分析

SQL> SHOW CON_NAME;

CON_NAME
------------------------------
CDB$ROOT

SQL> COL OWNER FOR A10
SQL> select con_id,owner,object_type from cdb_objects where object_name='PDB_SPFILE$';

    CON_ID OWNER      OBJECT_TYPE
---------- ---------- -----------------------
         2 SYS        TABLE
         1 SYS        TABLE
         3 SYS        TABLE

SQL> COL DB_UNIQ_NAME FOR A10
SQL> COL NAME FOR A15
SQL> COL VALUE$ FOR A10
SQL> SELECT DB_UNIQ_NAME,PDB_UID,NAME,VALUE$ FROM PDB_SPFILE$;

DB_UNIQ_NA    PDB_UID NAME            VALUE$
---------- ---------- --------------- ----------
cdb        3313918585 sessions        101

SQL> ALTER SESSION SET CONTAINER=pdb1;

Session altered.

SQL>  SELECT DB_UNIQ_NAME,PDB_UID,NAME,VALUE$ FROM PDB_SPFILE$;

no rows selected

证明pdb中不同于root的参数是记录在root的PDB_SPFILE$基表中.
整个CDB的工作原理是如果在PDB_SPFILE$中无相关参数记录,则继承cdb的参数文件中值,如果PDB_SPFILE$中有记录则使用该值覆盖cdb参数文件值.

删除PDB_SPFILE$验证

SQL> SHOW CON_NAME;

CON_NAME
------------------------------
CDB$ROOT

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100

SQL>  select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4048821679 PDB$SEED                       READ ONLY
         3 3313918585 PDB1                           MOUNTED
         4 3872456618 PDB2                           READ WRITE

SQL> alter session set container=pdb2;

Session altered.

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100
SQL> alter system set open_cursors=110;

System altered.

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     110

SQL> conn / as sysdba
Connected.
SQL> select value$ from pdb_spfile$ where name='open_cursors';

VALUE$
--------------------------------------------------------------------------------
110

SQL> delete from  pdb_spfile$ where name='open_cursors';

1 row deleted.

SQL> commit;

Commit complete.

SQL> startup
ORACLE instance started.

Total System Global Area  597098496 bytes
Fixed Size                  2291072 bytes
Variable Size             272632448 bytes
Database Buffers          314572800 bytes
Redo Buffers                7602176 bytes
Database mounted.
Database opened.
SQL> select value$ from pdb_spfile$ where name='open_cursors';

no rows selected

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100
SQL> alter session set container=pdb2 ;

Session altered.

SQL> alter database open;

Database altered.

SQL>  show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100

删除PDB_SPFILE$中相关记录,pdb的参数值会自动继续继承cdb中参数值
总结说明:通过上述的一些列试验证明cdb中参数关系,在cdb中修改,会默认所有pdb均自动继承;如果在pdb中修改值会覆盖cdb参数,而且只对当前pdb生效,并记录在PDB_SPFILE$

ORACLE 12C ASM 新特性:共享密码文件

在ORACLE 12C之前大家都知道密码文件是存放在?/dbs或者?/database中,如果要修改修改sysdba权限的用户密码时候,会去修改密码文件,而在rac数据库的sys密码文件是存在各个节点中,这个时候修改sysdba权限的密码就需要在两个节点都要做同样的操作,而对于数据库来说本身是只要在一个节点上修改即可,因为密码是记录在user$中,就是因为密码文件非共享且在各个节点中都有,因此需要在各个节点均要执行修改密码命令,确保密码文件被正常修改。因为rac 密码文件非共享的机制存在,导致修改sysdba权限密码繁琐,有些时候甚至有节点忘记修改,导致需要使用密码文件操作数据库的时候不能正常进行,DG传输日志异常等故障。在ORACLE 12C中为了解决这个问题,引入了密码文件可以存入ASM新特性,从而使得密码文件存储在ASM中实现所有节点共享,从而解决该问题.
ASM存储密码文件前提条件 COMPATIBLE.ASM>= 12.1
查询ASM信息

SQL>  select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

SQL> select NAME,COMPATIBILITY from v$asm_diskgroup;

NAME                           COMPATIBILITY
------------------------------ ------------------------------------------------------------
DATA                           12.1.0.0.0

查询crs中关于db配置

[grid@xifenfei ~]$  srvctl config database -d cdb
Database unique name: cdb
Database name: cdb
Oracle home: /u01/app/oracle/product/12.1/db_1
Oracle user: oracle
Spfile: +DATA/cdb/spfilecdb.ora
Password file: 
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: MANUAL
Database instance: cdb
Disk Groups: DATA
Services: 

这里db的password file为空,即表示使用默认值,也就是为$ORACLE_HOME/dbs/orapwxifenfei

创建密码文件存储在ASM中

--创建db新密码文件
[oracle@xifenfei ~]$ orapwd file='+data/CDB/orapwdxifenfei' dbuniquename='cdb'

Enter password for SYS: 
----输入sys用户密码

--创建asm新密码文件
orapwd file='+data/ASM/orapwasm' asm=y
----asm=y 表示创建的密码文件为asm的


--使用老密码文件创建db/asm新密码文件
orapwd input_file='/oraclegrid/dbs/orapwasm' file='+data/ASM/orapwasm' [asm=y]
----input_file 表示使用老的密码文件创建新的存储在ASM中的密码文件

查看ASM中密码文件

ASMCMD> showversion
ASM version         : 12.1.0.1.0
ASMCMD> pwd
+data/cdb
ASMCMD>  ls -l orapwdxifenfei
Type      Redund  Striped  Time             Sys  Name
PASSWORD  UNPROT  COARSE   MAY 31 19:00:00  N    orapwdxifenfei => +DATA/CDB/PASSWORD/pwdcdb.290.816897265

配置crs中password file项

[grid@xifenfei ~]$ srvctl modify database -db cdb -pwfile  +data/CDB/orapwdxifenfei

查询crs中关于db配置

[grid@xifenfei ~]$  srvctl config database -d cdb
Database unique name: cdb
Database name: cdb
Oracle home: /u01/app/oracle/product/12.1/db_1
Oracle user: oracle
Spfile: +DATA/cdb/spfilecdb.ora
Password file: +data/CDB/orapwdxifenfei
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: MANUAL
Database instance: cdb
Disk Groups: DATA
Services: 

至此数据库启动使用密码ASM中的密码文件完成,补充说明,该方式配置在ASM中的密码文件,只能是通过crs方式启动db才会生效,如果手工使用sqlplus启动数据库不会使用该密码文件,还是使用默认密码文件。这里也就提醒大家操作规范:在RAC环境(包含单节点的GI环境)中,对数据库的启动关闭操作强烈建议使用crs相关命令来完成,而不推荐使用sqlplus命令

跳过rman坏块恢复

在有些情况下,我们仅有一份rman备份,而这个时候rman 备份有出现坏块,使得我们的还原/恢复工作无法继续下去,导致数据大量丢失.我们可以通过设置event 19548/19549来跳过坏块,最大程度抢救数据
rman备份数据文件

C:\Users\XIFENFEI>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 6 20:31:19 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: XIFENFEI (DBID=1422012639)

RMAN> backup tablespace users format 'f:/users_bak.rman';

Starting backup at 06-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=197 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 06-JUN-13
channel ORA_DISK_1: finished piece 1 at 06-JUN-13
piece handle=F:\USERS_BAK.RMAN tag=TAG20130606T203154 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 06-JUN-13

切换归档日志

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\oracle\product\11.2.0\dbhome_1\RDBMS
Oldest online log sequence     95
Next log sequence to archive   97
Current log sequence           97

重命名数据文件

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--------------------------------------
e:\oracle\oradata\XIFENFEI>move USERS01.DBF USERS01_bak.DBF
移动了         1 个文件。
--------------------------------------

SQL> startup
ORACLE instance started.

Total System Global Area  418484224 bytes
Fixed Size                  1385052 bytes
Variable Size             327159204 bytes
Database Buffers           83886080 bytes
Redo Buffers                6053888 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF'

破坏备份集
破坏前


破坏后


这里很明显,我通过ue把rman备份集中的T修改为了A,肯定破坏了文件,使之出现坏块

rman还原数据文件

C:\Users\XIFENFEI>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 6 21:02:41 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: XIFENFEI (DBID=1422012639, not open)

RMAN> restore datafile 4;

Starting restore at 06-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 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 00004 to E:\ORACLE\ORADATA\XIFENFEI\USERS
01.DBF
channel ORA_DISK_1: reading from backup piece F:\USERS_BAK.RMAN
channel ORA_DISK_1: ORA-19870: error while restoring backup piece F:\USERS_BAK.R
MAN
ORA-19612: datafile 4 not restored due to missing or corrupt data

failover to previous backup

creating datafile file number=4 name=E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF
Finished restore at 06-JUN-13

这里可以清晰的看到rman报ORA-19612错误,restore 失败,alert日志为:

Thu Jun 06 21:02:31 2013
ALTER DATABASE OPEN
Errors in file E:\ORACLE\diag\rdbms\xifenfei\xff\trace\xff_dbw0_7400.trc:
ORA-01157: ????/?????? 4 - ??? DBWR ????
ORA-01110: ???? 4: 'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF'
ORA-27041: ??????
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。
Errors in file E:\ORACLE\diag\rdbms\xifenfei\xff\trace\xff_ora_4272.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF'
ORA-1157 signalled during: ALTER DATABASE OPEN...
Thu Jun 06 21:02:33 2013
Checker run found 1 new persistent data failures
Thu Jun 06 21:03:23 2013
Corrupt block 101 found during reading backup piece, file=F:\USERS_BAK.RMAN, corr_type=3
Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data
Continuing reading piece F:\USERS_BAK.RMAN, no other copies available.

rman备份集有坏块,导致rman还原无法正常进行下去,还原后的数据文件大小


观察已经正常还原出来数据文件情况

SQL> select CHECKPOINT_CHANGE#,file# from v$datafile_header;

CHECKPOINT_CHANGE#      FILE#
------------------ ----------
           1571582          1
           1571582          2
           1571582          3
             18379          4
           1571582          5
           1571582          6
           1571582          7

SQL> recover database datafile 4 ;
ORA-00274: illegal recovery option DATAFILE


SQL> recover datafile 4;
ORA-00279: change 18379 generated at 01/20/2013 17:13:56 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000001_0805223583.0001
ORA-00280: change 18379 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

rman只是还原了很小的一部分文件,做恢复提示需要从归档日志seq 1开始(某些情况可能需要其他归档,总之不是正常情况),证明rman还原异常

设置event事件还原

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup pfile='e:/pfile.txt' mount;
ORACLE instance started.

Total System Global Area  418484224 bytes
Fixed Size                  1385052 bytes
Variable Size             327159204 bytes
Database Buffers           83886080 bytes
Redo Buffers                6053888 bytes
Database mounted.
SQL> show parameter event;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string      19548 trace name context forev
                                                 er, 19549 trace name context f
                                                 orever
Event 19548:This will attempt to restore content of the corrupted block if it is possible.
Event 19549:This will suppress erroring out during restore

rman还原数据文件

RMAN> restore datafile 4;

Starting restore at 06-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 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 00004 to E:\ORACLE\ORADATA\XIFENFEI\USERS
01.DBF
channel ORA_DISK_1: reading from backup piece F:\USERS_BAK.RMAN
channel ORA_DISK_1: piece handle=F:\USERS_BAK.RMAN tag=TAG20130606T203154
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 06-JUN-13

这里证明数据库rman有坏块通过rman还原成功,alert日志提示如下

Thu Jun 06 21:29:53 2013
WARNING: The block that appears to be block number 100
         in file 4 is corrupt in backup piece F:\USERS_BAK.RMAN.
         Such blocks would usually be formatted as empty
         in the restored file, but event 19548 has been
         set to include the block as-is in the restored
         file.
Corrupt block 102 found during reading backup piece, file=F:\USERS_BAK.RMAN, corr_type=-2
Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data
Continuing reading piece F:\USERS_BAK.RMAN, no other copies available.
…………
Corrupt block 258 found during reading backup piece, file=F:\USERS_BAK.RMAN, corr_type=-2
Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data
Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data
Continuing reading piece F:\USERS_BAK.RMAN, no other copies available.
WARNING: some data in the backup of file 4 was missing
         or corrupt.  Event 19549 has been set to allow
         the file to be restored anyway.
           backup header block count: 5369
           backup actual block count: 5212
              backup header checksum: -218250743
              backup actual checksum: 1442665538
Full restore complete of datafile 4 E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF.  Elapsed time: 0:00:25 
  checkpoint is 1570136
  last deallocation scn is 1508457

这里rman还原依然遇到很多坏块,但是均跳过坏块,还是完整的恢复出来的数据文件(大小)


rman还原数据文件

RMAN> recover datafile 4;

Starting recover at 06-JUN-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 94 is already on disk as file E:\ORACLE\
PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000094_0805223583.0001
archived log for thread 1 with sequence 95 is already on disk as file E:\ORACLE\
PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000095_0805223583.0001
archived log for thread 1 with sequence 96 is already on disk as file E:\ORACLE\
PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000096_0805223583.0001
archived log file name=E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000094_080
5223583.0001 thread=1 sequence=94
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-JUN-13

这里可以明显的看到在recover过程中数据库应用的是备份后的所有归档,数据文件是正常被还原出来(坏块除外)

查询对象

SQL> alter database open;

Database altered.

SQL> conn test/test
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
STB101                         TABLE

SQL> select count(*) from stb101;
select count(*) from stb101
                     *
ERROR at line 1:
ORA-08103: object no longer exists

dbv检查坏块

e:\oracle\oradata\XIFENFEI>dbv file=USERS01.DBF

DBVERIFY: Release 11.2.0.3.0 - Production on Thu Jun 6 23:59:49 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF

Page 100 is marked corrupt
Corrupt block relative dba: 0x01000064 (file 4, block 100)
Bad check value found during dbv:
Data in bad block:
 type: 30 format: 2 rdba: 0x01000064
 last change scn: 0x0000.00004890 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x48901e01
 check value in block header: 0x8311
 computed block checksum: 0x20



DBVERIFY - Verification complete

Total Pages Examined         : 12320
Total Pages Processed (Data) : 4952
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 7069
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 298

证明设置了event之后,rman确实跳过了备份集中的坏块,而且是直接还原了坏块内容,证明了event 19548和19549作用

补充说明
在非特殊情况下强烈不建议设置相关event跳过rman中的坏块来还原/恢复数据库,这样将对数据的丢失,甚至数据库是否可以正常open不好评估,rman备份重要,确保rman备份可用也很重要.