生产库备份遇到
ORA-01400 错误~
当rman 不使用 catalog时,RMAN 命令可以正常实施~！
当rman 备份使用catalog数据库时,执行rman 命令总是报错:ORA-01400:cannot insert NULL into ("RMAN_
YANGDB"."RLH"."LOW_TIME")
RMAN> resync catalog;
starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 01/06/2012 10:39:11
ORA-01400: cannot insert NULL into ("RMAN_YANGDB"."RLH"."LOW_TIME")
报错的原因是因为：不能向恢复目录数据库的表RLH插入空值“the issue caused by unable to insert NULL value in RSR table of the recovery catalog.”（至于为什么会产生 thread# 为0的记录，这个待查！）
执行如下查询，查看v$log_history的记录中thread#为0的记录！
select thread#, sequence#, first_change#, next_change#, first_time from v$log_history where thread#=0;
THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# FIRST_TIME
------- -------- ------------- ------------ ------------------
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
解决方法！
因为v$log_history 记录中含有空值，v$log_history是有系统自己管理的动态性能视图，所以使用oracle提供的
dbms_backup_restore.resetCfileSection()包来清除控制文件中关于log_history 的记录，让系统重新生成新的日志记录！
1 使用如下命令查询LOG HISTORY在控制文件中的编号为 9 ！v$controlfile_record_section 视图记录了控制文件里包含的信息,以及各个项目的当前使用情况
17:31:18 yang(40)@yangdb> select rownum-1 session_id, type from v$controlfile_record_section;
SESSION_ID TYPE
---------- ----------------------------
0 DATABASE
1 CKPT PROGRESS
2 REDO THREAD
3 REDO LOG
4 DATAFILE
5 FILENAME
6 TABLESPACE
7 TEMPORARY FILENAME
8 RMAN CONFIGURATION
9 LOG HISTORY
10 OFFLINE RANGE
11 ARCHIVED LOG
12 BACKUP SET
13 BACKUP PIECE
14 BACKUP DATAFILE
15 BACKUP REDOLOG
16 DATAFILE COPY
17 BACKUP CORRUPTION
18 COPY CORRUPTION
19 DELETED OBJECT
20 PROXY COPY
21 BACKUP SPFILE
22 DATABASE INCARNATION
23 FLASHBACK LOG
24 RECOVERY DESTINATION
25 INSTANCE SPACE RESERVATION
26 REMOVABLE RECOVERY FILES
27 RMAN STATUS
28 THREAD INSTANCE NAME MAPPING
29 MTTR
30 DATAFILE HISTORY
31 STANDBY DATABASE MATRIX
32 GUARANTEED RESTORE POINT
33 RESTORE POINT
34 DATABASE BLOCK CORRUPTION
35 ACM OPERATION
36 FOREIGN ARCHIVED LOG
37 rows selected.
2. 使用上面查询出来的 "LOG HISTORY" 的session_id ,使用dbms_backup_restore.resetCfileSection(section_id);:
SQL> execute dbms_backup_restore.resetCfileSection(9);
在清理控制文件中 "LOG HISTORY"部分之后后,使用rman 连接catalog 进行同步catalog~
$ rman target sys/password@alias_target catalog rman_user/password@alias_catalog
RMAN> resync catalog;
关于v$CONTROLFILE_RECORD_SECTION 是v_$CONTROLFILE_RECORD_SECTION的同义词。该表中记录了数据库中的各种信息，备份的信息，redo信息，归档信息，数据文件信息~
17:31:19 yang(40)@yangdb> set pages 50000
21:10:19 yang(40)@yangdb> col type for a30
21:10:19 yang(40)@yangdb> col PCT_USED format 990.09
21:10:19 yang(40)@yangdb> -- Controlfile creation parameters:
21:10:19 yang(40)@yangdb> -- Type DATAFILE is for MAXDATAFILES
21:10:19 yang(40)@yangdb> -- Type REDO LOG is for MAXLOGFILES
21:10:19 yang(40)@yangdb> -- Type LOG HISTORY is for MAXLOGHISTORY
21:10:19 yang(40)@yangdb> -- Type REDO THREAD is for MAXINSTANCES
21:10:19 yang(40)@yangdb> -- No entry for MAXLOGMEMBERS here, use: select dimlm max_log_members from X$KCCDI;
21:10:19 yang(40)@yangdb> select type,records_used,records_total,records_used/records_total*100 "PCT_USED" from sys.v_$CONTROLFILE_RECORD_SECTION order by 1;
TYPE RECORDS_USED RECORDS_TOTAL PCT_USED
------------------------------ ------------ ------------- --------
ACM OPERATION 6 64 9.38
ARCHIVED LOG 179 224 79.91
BACKUP CORRUPTION 0 371 0.00
BACKUP DATAFILE 245 245 100.00
BACKUP PIECE 200 200 100.00
BACKUP REDOLOG 199 215 92.56
BACKUP SET 304 409 74.33
BACKUP SPFILE 46 131 35.11
CKPT PROGRESS 0 11 0.00
COPY CORRUPTION 0 409 0.00
DATABASE 1 1 100.00
DATABASE BLOCK CORRUPTION 0 8384 0.00
DATABASE INCARNATION 2 292 0.68
DATAFILE 7 100 7.00
DATAFILE COPY 3 200 1.50
DATAFILE HISTORY 0 57 0.00
DELETED OBJECT 818 818 100.00
FILENAME 12 2298 0.52
FLASHBACK LOG 0 2048 0.00
FOREIGN ARCHIVED LOG 0 1002 0.00
GUARANTEED RESTORE POINT 0 2048 0.00
INSTANCE SPACE RESERVATION 1 1055 0.09
LOG HISTORY 292 292 100.00
MTTR 1 8 12.50
OFFLINE RANGE 0 163 0.00
PROXY COPY 0 246 0.00
RECOVERY DESTINATION 1 1 100.00
REDO LOG 3 16 18.75
REDO THREAD 1 8 12.50
REMOVABLE RECOVERY FILES 6 1000 0.60
RESTORE POINT 0 2083 0.00
RMAN CONFIGURATION 2 50 4.00
RMAN STATUS 451 451 100.00
STANDBY DATABASE MATRIX 31 31 100.00
TABLESPACE 7 100 7.00
TEMPORARY FILENAME 2 100 2.00
THREAD INSTANCE NAME MAPPING 8 8 100.00
37 rows selected.
2 关于dbms_backup_restore.resetCfileSection的作用是将控制文件相关区域中的内容清空:(注意，此操作为测试环境,在无oracle技术支持建议或允许的情况下,请勿在生产环境测试，以下操作是在数据库open状态下执行）
17:04:26 SYS@yangdb> execute dbms_backup_restore.resetCfileSection(9);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
17:04:37 SYS@yangdb> select count(1) from v$log_history;
COUNT(1)
----------
0
17:26:55 YANG@yangdb> update bigtab set object_id=id where rownum
9999 rows updated.
Elapsed: 00:00:00.44
17:26:57 YANG@yangdb> commit;
Commit complete.
Elapsed: 00:00:00.01
17:27:04 YANG@yangdb>
17:27:04 YANG@yangdb> conn /as sysdba
Connected.
17:27:10 SYS@yangdb>
17:27:10 SYS@yangdb>
17:27:10 SYS@yangdb> select count(1) from v$log_history;
COUNT(1)
----------
62
1 row selected.
Elapsed: 00:00:00.03
17:27:15 SYS@yangdb> execute dbms_backup_restore.resetCfileSection(9);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
17:27:21 SYS@yangdb> select count(1) from v$log_history;
COUNT(1)
----------
0
1 row selected.
Elapsed: 00:00:00.01
17:27:23 SYS@yangdb>
