最近遇到一个很有意思的问题,在RMAN中手动配置一条信息,但是由于笔误,错误的增加一行“FOR DEVICE TYPE 'DISK' TO 'AA_auto_control_expire_backup_%F.ctl';”
但是希望删除的过程,遇到了问题,通过各种clear的方式,都不能成功删除,怀疑是oracle的一个小bug
1. 笔误之后的结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
RMAN> show
all
;
使用目标数据库控制文件替代恢复目录
db_unique_name 为 ORCL 的数据库的 RMAN 配置参数为:
CONFIGURE RETENTION POLICY
TO
REDUNDANCY 1; #
default
CONFIGURE BACKUP OPTIMIZATION
OFF
; #
default
CONFIGURE
DEFAULT
DEVICE TYPE
TO
DISK; #
default
CONFIGURE CONTROLFILE AUTOBACKUP
OFF
;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
FOR
DEVICE TYPE
'DISK'
TO
'AA_auto_control_expire_backup_%F.ctl'
;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
FOR
DEVICE TYPE DISK
TO
'%F'
; #
default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE
TO
BACKUPSET; #
default
CONFIGURE DATAFILE BACKUP COPIES
FOR
DEVICE TYPE DISK
TO
1; #
default
CONFIGURE ARCHIVELOG BACKUP COPIES
FOR
DEVICE TYPE DISK
TO
1; #
default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
'clear'
;
CONFIGURE MAXSETSIZE
TO
UNLIMITED; #
default
CONFIGURE ENCRYPTION
FOR
DATABASE
OFF
; #
default
CONFIGURE ENCRYPTION ALGORITHM
'AES128'
; #
default
CONFIGURE COMPRESSION ALGORITHM
'BASIC'
AS
OF
RELEASE
'DEFAULT'
OPTIMIZE
FOR
LOAD
TRUE
; #
default
CONFIGURE ARCHIVELOG DELETION POLICY
TO
NONE; #
default
CONFIGURE SNAPSHOT CONTROLFILE
NAME
TO
'C:\APP\XIAOLXU\PRODUCT\11.2.0\DBHOME_2\DATABASE\SNCFORCL.ORA'
; #
default
|
2. 在无法删除之后,想到RMAN的信息是记录在control file中,dump一个trace看看。
1
|
SQL>
alter
database
backup control file
to
trace
|
在alert log中可以看到trace的文件名
1
2
3
4
5
|
Wed Jun 29 16:48:14 2016
CJQ0 started
with
pid=28, OS id=16764
alter
database
backup controlfile
to
trace
Backup controlfile written
to
trace file C:\APP\XIAOLXU\diag\rdbms\orcl\orcl\trace\orcl_ora_6304.trc
Completed:
alter
database
backup controlfile
to
trace
|
3. trace内容如下, 可以看到对应的记录信息
“FOR DEVICE TYPE 'DISK' TO 'AA_auto_control_expire_backup_%F.ctl';”
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
STARTUP NOMOUNT
CREATE
CONTROLFILE REUSE
DATABASE
"ORCL"
RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP
1
'C:\APP\XIAOLXU\ORADATA\ORCL\REDO01.LOG'
SIZE
50M BLOCKSIZE 512,
GROUP
2
'C:\APP\XIAOLXU\ORADATA\ORCL\REDO02.LOG'
SIZE
50M BLOCKSIZE 512,
GROUP
3
'C:\APP\XIAOLXU\ORADATA\ORCL\REDO03.LOG'
SIZE
50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'C:\APP\XIAOLXU\ORADATA\ORCL\SYSTEM01.DBF'
,
'C:\APP\XIAOLXU\ORADATA\ORCL\SYSAUX01.DBF'
,
'C:\APP\XIAOLXU\ORADATA\ORCL\UNDOTBS01.DBF'
,
'C:\APP\XIAOLXU\ORADATA\USERTEST.DBF'
CHARACTER
SET
AL32UTF8
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(
'CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE'
,
''
'DISK'
' TO '
'AA_auto_control_expire_backup_%F.ctl'
''
); <<<<<<<<
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(
'CHANNEL'
,
'DEVICE TYPE DISK FORMAT '
'clear'
''
);
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE
:RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(
'CONTROLFILE AUTOBACKUP'
,
'OFF'
);
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE 'C:\APP\XIAOLXU\PRODUCT\11.2.0\DBHOME_2\RDBMS\ARC0000000001_0898420407.0001';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER
DATABASE
USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER
DATABASE
OPEN
RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER
TABLESPACE
TEMP
ADD
TEMPFILE
'C:\APP\XIAOLXU\ORADATA\ORCL\TEMP01.DBF'
SIZE
60817408 REUSE AUTOEXTEND
ON
NEXT
655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
|
4. 使用下面命令重建控制文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
STARTUP NOMOUNT
CREATE
CONTROLFILE REUSE
DATABASE
"ORCL"
NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP
1
'C:\APP\XIAOLXU\ORADATA\ORCL\REDO01.LOG'
SIZE
50M BLOCKSIZE 512,
GROUP
2
'C:\APP\XIAOLXU\ORADATA\ORCL\REDO02.LOG'
SIZE
50M BLOCKSIZE 512,
GROUP
3
'C:\APP\XIAOLXU\ORADATA\ORCL\REDO03.LOG'
SIZE
50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'C:\APP\XIAOLXU\ORADATA\ORCL\SYSTEM01.DBF'
,
'C:\APP\XIAOLXU\ORADATA\ORCL\SYSAUX01.DBF'
,
'C:\APP\XIAOLXU\ORADATA\ORCL\UNDOTBS01.DBF'
,
'C:\APP\XIAOLXU\ORADATA\USERTEST.DBF'
CHARACTER
SET
AL32UTF8
|
5. 重建完成之后,这条信息就被删除掉了。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
RMAN> show
all
;
使用目标数据库控制文件替代恢复目录
db_unique_name 为 ORCL 的数据库的 RMAN 配置参数为:
CONFIGURE RETENTION POLICY
TO
REDUNDANCY 1; #
default
CONFIGURE BACKUP OPTIMIZATION
OFF
; #
default
CONFIGURE
DEFAULT
DEVICE TYPE
TO
DISK; #
default
CONFIGURE CONTROLFILE AUTOBACKUP
OFF
; #
default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
FOR
DEVICE TYPE DISK
TO
'%F'
; #
default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE
TO
BACKUPSET; #
default
CONFIGURE DATAFILE BACKUP COPIES
FOR
DEVICE TYPE DISK
TO
1; #
default
CONFIGURE ARCHIVELOG BACKUP COPIES
FOR
DEVICE TYPE DISK
TO
1; #
default
CONFIGURE MAXSETSIZE
TO
UNLIMITED; #
default
CONFIGURE ENCRYPTION
FOR
DATABASE
OFF
; #
default
CONFIGURE ENCRYPTION ALGORITHM
'AES128'
; #
default
CONFIGURE COMPRESSION ALGORITHM
'BASIC'
AS
OF
RELEASE
'DEFAULT'
OPTIMIZE
FOR
LOAD
TRUE
; #
default
CONFIGURE ARCHIVELOG DELETION POLICY
TO
NONE; #
default
CONFIGURE SNAPSHOT CONTROLFILE
NAME
TO
'C:\APP\XIAOLXU\PRODUCT\11.2.0\DBHOME_2\DATABASE\SNCFORCL.ORA'
; #
default
|
6. 同理,如果有之前的control file备份,restore一个,问题也是可以解决的。
注意,以上是测试步骤,重建控制文件之后,还有些后续工作,register archive log,recover database还是需要做的。生产环境要慎重。
本文转自 hsbxxl 51CTO博客,原文链接:http://blog.51cto.com/hsbxxl/1794271,如需转载请自行联系原作者