唉。最近问题很多呀

1
ALL_CONSTRAINTS中有如下DEFERRABLE 和DEFERRED 两个字段
DEFERRABLE VARCHAR2(14) Whether the constraint is deferrable
deferrable & not deferrable
DEFERRED VARCHAR2(9) Whether the constraint was initially deferred
immediate & deferred我对DEFERRABLE 和DEFERRED 这两个字段的理解有点模糊，我在这里大概说一下，大家帮忙看看对不对。
DEFERRABLE 就是指定这个约束能不能延迟，如果指定not deferrable ，即非延迟的话，那么DEFERRED属性就没有任何意义了。
当DEFERRABLE 指定为DEFERRABLE 时，DEFERRED才起作用，这个时候如果指定了immediate就表明语句结束后立即进行验证是否符合约束，而如果指定deferred，则是在提交的时候进行验证。2
V$BACKUP_FILES里面有如下BACKUP_TYPE和STATUS两个字段
BACKUP_TYPE VARCHAR2(32) Type of the backup:
BACKUP SET & COPY & PROXY COPYSTATUS VARCHAR2(16) Status of the backup:
AVAILABLE & UNAVAILABLE & EXPIRED & OTHER我想问的是PROXY COPY是什么意思？
还有UNAVAILABLE 和EXPIRED 有什么区别？OTHER又代表了什么情况？
最近问题比较多，而且有的比较怪，麻烦各位了！

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

对，兴趣是最好的老师。我也是入了oracle的迷。
首先解释第一个约束的问题,
约束有两种类型:
1: 延迟型
2:非延迟型类型
10g数据库缺省的是非延迟约束.
okey,两者的区别是什么?
如果规则是延迟型约束的时候,
你做一个语句
for example:
update cc set name="newname" where name="oldname";
这个语句执行的时候,oracle不会立即检查他的约束条件,而等待 commit的时候才检查.
同样情况下,如果是非延迟约束的话,你执行了这个语句就马上检查它的约束条件了.不需要等到commit.那么not defferred(A) 和 defferred immediate(B)有什么区别呢?
我们假设defferred 某个时间为"C",效果上A=B,既执行了一个dml语句默认就立即检查
它的约束性.但是 B可以立即转成C,A就不可以.
第二个问题第一小问:Proxy copy is a feature of RMAN
RMAN sends the Media Management Vendor (MMV) a list of Oracle datafiles  to backup, rather than sending the data itself. This allows the MMV to implement optimized backup and restore strategies. One example of this is EMC's split-mirror BCV backups.
翻译一下：
proxy copy 是rman的一个特性，
rman 发送给MMV 一个需要备份的文件列表，而不是文件本身。
这允许mmv来做优化的备份和还原策略.一个使用proxy copy例子就是emc的 split mirror bcv 备份。我理解proxy copy是基于第三方软件的rman备份策略.
类似的有 BE(BACKUP EXEC),NBU(NETWORK BACKUP),CA(CA BRIGHTSTOR)备份软件提供的相关rman备份功能。等待高手指正了.
expired ,表示这个备份块丢失了,从下面的实验可以看出:[oracle@yysf ~]$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Tue Sep 22 19:38:32 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.connected to target database: ORCL (DBID=1219081979)RMAN> list backupset;using target database control file instead of recovery catalogList of Backup Sets
===================BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    350.70M    DISK        00:00:19     22-SEP-09
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20090922T184908
        Piece Name: /oracle/oradata/ORCL/backupset/2009_09_22/o1_mf_nnndf_TAG20090922T184908_5ckbx53s_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 368406     22-SEP-09 /oracle/oradata/orcl/system01.dbfBS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    6.80M      DISK        00:00:02     22-SEP-09
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20090922T184908
        Piece Name: /oracle/oradata/ORCL/backupset/2009_09_22/o1_mf_ncsnf_TAG20090922T184908_5ckby0g0_.bkp
  Control File Included: Ckp SCN: 368419       Ckp time: 22-SEP-09
  SPFILE Included: Modification time: 22-SEP-09
[oracle@yysf ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 19:39:06 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL> select file_type,status from v$backup_files;FILE_TYPE                        STATUS
-------------------------------- ----------------
DATAFILE
PIECE                            AVAILABLE
SPFILE
CONTROLFILE
PIECE                            AVAILABLESQL> quit[oracle@yysf ~]$ rm /oracle/oradata/ORCL/backupset/2009_09_22/o1_mf_ncsnf_TAG20090922T184908_5ckby0g0_.bkp
[oracle@yysf ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 19:39:06 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.SQL> select file_type,status from v$backup_files;FILE_TYPE                        STATUS
-------------------------------- ----------------
DATAFILE
PIECE                            AVAILABLE
SPFILE
CONTROLFILE
PIECE                            AVAILABLE[oracle@yysf ~]$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Tue Sep 22 19:40:02 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.connected to target database: ORCL (DBID=1219081979)RMAN> crosscheck backupset;using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oracle/oradata/ORCL/backupset/2009_09_22/o1_mf_nnndf_TAG20090922T184908_5ckbx53s_.bkp recid=1 stamp=698266149
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oracle/oradata/ORCL/backupset/2009_09_22/o1_mf_ncsnf_TAG20090922T184908_5ckby0g0_.bkp recid=2 stamp=698266176
Crosschecked 2 objects
RMAN> quit
Recovery Manager complete.
[oracle@yysf ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 19:40:14 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL>  select file_type,status from v$backup_files;FILE_TYPE                        STATUS
-------------------------------- ----------------
DATAFILE
PIECE                            AVAILABLE
SPFILE
CONTROLFILE
PIECE                            EXPIRED
谢谢回答
其实我看到过一些对于UNAVAILABLE & EXPIRED的描述，不过都很模糊。
比如说备份文件损坏了无法访问，就是UNAVAILABLE ，如果直接删掉了，像你做的实验那样，就是EXPIRED。
至于OTHERS么，我猜可能就是其他一些系统无法识别的情况吧。对于PROXY COPY，认同你的观点。
unavailable表示文件集失效了,那么下次还原的时候rman会跳过这个backupset,
你可以手动来把一个文件集改成无效,(但是不需要删除它)
这样做有什么实际意义呢?当你还原的时候,你不想还原最近的backupset里的数据,
而想还原最近备份集合之前的那个备份数据,
怎么做,就可以利用这个命令,
把最近的一个备份集设置为无效状态.
那么还原的时候就可以跳过最近一个backupset了,
完了可以再改回了哈....关于unavailable的测试如下:
[oracle@yysf ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 20:02:44 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL>  select file_type,status from v$backup_files;FILE_TYPE                        STATUS
-------------------------------- ----------------
DATAFILE
PIECE                            AVAILABLE
SPFILE
CONTROLFILE
PIECE                            EXPIRED
DATAFILE
PIECE                            AVAILABLE
CONTROLFILE
SPFILE
PIECE                            AVAILABLE
DATAFILEFILE_TYPE                        STATUS
-------------------------------- ----------------
PIECE                            AVAILABLE
CONTROLFILE
SPFILE
PIECE                            AVAILABLE15 rows selected.SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@yysf ~]$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Tue Sep 22 20:02:59 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.connected to target database: ORCL (DBID=1219081979)RMAN> change backupset 6 unavailable;changed backup piece unavailable
backup piece handle=/home/oracle/06kptgnm_1_1 recid=6 stamp=698270455
Changed 1 objects to UNAVAILABLE status
RMAN> quit
[oracle@yysf ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 20:03:43 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL>  select file_type,status from v$backup_files;FILE_TYPE                        STATUS
-------------------------------- ----------------
DATAFILE
PIECE                            AVAILABLE
SPFILE
CONTROLFILE
PIECE                            EXPIRED
DATAFILE
PIECE                            AVAILABLE
CONTROLFILE
SPFILE
PIECE                            AVAILABLE
DATAFILEFILE_TYPE                        STATUS
-------------------------------- ----------------
PIECE                            AVAILABLE
CONTROLFILE
SPFILE
PIECE                            UNAVAILABLE15 rows selected.SQL> [oracle@yysf ~]$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Tue Sep 22 20:08:47 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.connected to target database: ORCL (DBID=1219081979)RMAN> change backupset 6 available;using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
changed backup piece available
backup piece handle=/home/oracle/06kptgnm_1_1 recid=6 stamp=698270455
Changed 1 objects to AVAILABLE status
RMAN> quit
Recovery Manager complete.
[oracle@yysf ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 20:09:03 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL>   select file_type,status from v$backup_files;FILE_TYPE                        STATUS
-------------------------------- ----------------
DATAFILE
PIECE                            AVAILABLE
SPFILE
CONTROLFILE
PIECE                            EXPIRED
DATAFILE
PIECE                            AVAILABLE
CONTROLFILE
SPFILE
PIECE                            AVAILABLE
DATAFILEFILE_TYPE                        STATUS
-------------------------------- ----------------
PIECE                            AVAILABLE
CONTROLFILE
SPFILE
PIECE                            AVAILABLE15 rows selected.SQL>
那如果备份文件发生了物理上的损坏，应该也是UNAVAILABLE吧？帖子就先放一放，看看哪位朋友还有什么补充没有，晚上回学校了结。
这个也可以测试的:
用rman备份一个文件,把备份集rm掉,再touch一个同名文件替代这个文件,
再crosscheck 一下,最后查看状态就可以了.
结果是 unavailable,过程就不写了.
学习了，这些理论还真深奥啊，看来做DBA不容易啊！
[oracle@yysf dbs]$ cp spfileorcl.ora /home/oracle
[oracle@yysf dbs]$ cd /home/oracle
[oracle@yysf ~]$ ls
05kptgms_1_1  06kptgnm_1_1  06kptgnm_1_1rename  database  spfileorcl.ora
[oracle@yysf ~]$ rm 06kptgnm_1_1
[oracle@yysf ~]$ mv spfileorcl.ora  06kptgnm_1_1
[oracle@yysf ~]$ ls
05kptgms_1_1  06kptgnm_1_1  06kptgnm_1_1rename  database
[oracle@yysf ~]$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Tue Sep 22 23:49:50 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.connected to target database: ORCL (DBID=1219081979, not open)RMAN> crosscheck backupset;using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oracle/oradata/ORCL/backupset/2009_09_22/o1_mf_nnndf_TAG20090922T184908_5ckbx53s_.bkp recid=1 stamp=698266149
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oracle/oradata/ORCL/backupset/2009_09_22/o1_mf_ncsnf_TAG20090922T184908_5ckby0g0_.bkp recid=2 stamp=698266176
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oracle/oradata/ORCL/backupset/2009_09_22/o1_mf_nnndf_TAG20090922T194708_5ckg9woz_.bkp recid=3 stamp=698269628
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oracle/oradata/ORCL/backupset/2009_09_22/o1_mf_ncsnf_TAG20090922T194708_5ckgbq6k_.bkp recid=4 stamp=698269655
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/05kptgms_1_1 recid=5 stamp=698270429
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/06kptgnm_1_1 recid=6 stamp=698270455
Crosschecked 6 objects
RMAN> list backupset;
List of Backup Sets
===================BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    350.70M    DISK        00:00:19     22-SEP-09
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20090922T184908
        Piece Name: /oracle/oradata/ORCL/backupset/2009_09_22/o1_mf_nnndf_TAG20090922T184908_5ckbx53s_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 368406     22-SEP-09 /oracle/oradata/orcl/system01.dbfBS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    6.80M      DISK        00:00:02     22-SEP-09
        BP Key: 2   Status: EXPIRED  Compressed: NO  Tag: TAG20090922T184908
        Piece Name: /oracle/oradata/ORCL/backupset/2009_09_22/o1_mf_ncsnf_TAG20090922T184908_5ckby0g0_.bkp
  Control File Included: Ckp SCN: 368419       Ckp time: 22-SEP-09
  SPFILE Included: Modification time: 22-SEP-09BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    350.70M    DISK        00:00:20     22-SEP-09
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20090922T194708
        Piece Name: /oracle/oradata/ORCL/backupset/2009_09_22/o1_mf_nnndf_TAG20090922T194708_5ckg9woz_.bkp
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 370761     22-SEP-09 /oracle/oradata/orcl/system01.dbfBS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    6.80M      DISK        00:00:02     22-SEP-09
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20090922T194708
        Piece Name: /oracle/oradata/ORCL/backupset/2009_09_22/o1_mf_ncsnf_TAG20090922T194708_5ckgbq6k_.bkp
  Control File Included: Ckp SCN: 370769       Ckp time: 22-SEP-09
  SPFILE Included: Modification time: 22-SEP-09BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    350.70M    DISK        00:00:20     22-SEP-09
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20090922T200028
        Piece Name: /home/oracle/05kptgms_1_1
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 371287     22-SEP-09 /oracle/oradata/orcl/system01.dbfBS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    6.80M      DISK        00:00:02     22-SEP-09
        BP Key: 6   Status: EXPIRED  Compressed: NO  Tag: TAG20090922T200028
        Piece Name: /home/oracle/06kptgnm_1_1
  Control File Included: Ckp SCN: 371295       Ckp time: 22-SEP-09
  SPFILE Included: Modification time: 22-SEP-09[oracle@yysf ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 23:50:42 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL> select file_type,status from v$backup_files;
select file_type,status from v$backup_files
                             *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> alter database open;Database altered.SQL> select file_type,status from v$backup_files;FILE_TYPE                        STATUS
-------------------------------- ----------------
DATAFILE
PIECE                            AVAILABLE
SPFILE
CONTROLFILE
PIECE                            EXPIRED
DATAFILE
PIECE                            AVAILABLE
CONTROLFILE
SPFILE
PIECE                            AVAILABLE
DATAFILEFILE_TYPE                        STATUS
-------------------------------- ----------------
PIECE                            AVAILABLE
CONTROLFILE
SPFILE
PIECE                            EXPIRED
好象不行...... 还是expired
呵呵其实做什么想做好都不容易就看自己是不是真的愿意去做了
不过DBA确实需要很多方面的知识