RMAN还原后控制文件与数据文件scn不一致案例处理
一、recover之后无法open处理
源数据库：11.2.0.4 rac
目标数据库：11.2.0.4单实例
场景：将RAC的RMAN 0级备份还原到单实例数据库
1、在前面restore之后恢复数据库
RMAN> recover database;
Starting recover at 2019/09/11 17:41:08
using channel ORA_DISK_1
starting media recovery
unable to find archived log
archived log thread=1 sequence=926
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/11/2019 17:41:08
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 926 and starting SCN of 1457893452
2、打开数据库
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/oradata/sysaux.271.1008536053'
无法打开数据库
3、查看控制文件和数据文件scn
SQL> select CHECKPOINT_CHANGE#,count(*) from V$DATAFILE_HEADER group by CHECKPOINT_CHANGE#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ----------
1457893452 2
1457893454 2
1457893455 2
1457893456 2
1457893457 2
1457893458 2
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;
FILE# TO_CHAR(CHECK
---------- -------------
1 1457893452
2 1457893454
3 1457893455
4 1457893456
5 1457893457
6 1457893458
7 1457893452
8 1457893455
9 1457893456
10 1457893457
11 1457893458
12 1457893454
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;
FILE# TO_CHAR(CHECK
---------- -------------
1 1457893452
2 1457893454
3 1457893455
4 1457893456
5 1457893457
6 1457893458
7 1457893452
8 1457893455
9 1457893456
10 1457893457
11 1457893458
12 1457893454
可以看到数据文件与控制文件scn有很多不一致
4、使用隐藏参数尝试打开
pfile中加入如下参数启动
_allow_resetlogs_corruption= TRUE
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 2 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 2 thread 1: '+data'
可以发现还是无法open，原因是原先的redo是放在ASM中，在resetlogs时去重新创建redo，但是找不到asm磁盘组，就报错了。
5、通过重建控制文件，来改动redo目录
先生成控制文件trace
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE as '/oradata/control_bak.ctl';
修改trace文件中redo目录
修改为如下命令启动：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ZJEDB" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 9344
LOGFILE
GROUP 1 '/oradata/redo1.log' SIZE 100M BLOCKSIZE 512,
GROUP 2 '/oradata/redo2.log' SIZE 100M BLOCKSIZE 512,
GROUP 3 '/oradata/redo3.log' SIZE 100M BLOCKSIZE 512
DATAFILE
'/oradata/system.268.1008532611',
'/oradata/sysaux.271.1008536053',
'/oradata/undotbs1.272.1008536079',
'/oradata/undotbs2.273.1008536079',
'/oradata/users.277.1008536225',
'/oradata/zjzwfw01.dbf',
'/oradata/zjzwfw02.dbf',
'/oradata/zjzwfw03.dbf',
'/oradata/zjzwfw04.dbf',
'/oradata/zjzwfw05.dbf',
'/oradata/zjzwfw06.dbf',
'/oradata/zjzwfw07.dbf',
'/oradata/zjzwfw08.dbf',
'/oradata/zjzwfw09.dbf',
'/oradata/zjzwfw10.dbf',
'/oradata/system.270.1008536045',
'/oradata/zjzwfw11.dbf',
'/oradata/zjzwfw12.dbf',
'/oradata/zjzwfw13.ora',
'/oradata/zjzwfw14.dbf',
'/oradata/zjzwfw15.dbf'
CHARACTER SET ZHS16GBK
;
重建控制文件成功
Control file created.
6、再次打开数据库
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
还是无法打开数据库，此报错原因是oracle的一个bug，在MOS ID 334899.1可查到
RMAN Duplicate from RAC backup fails ORA-38856 (文档 ID 334899.1)
查看thread2已经不存在
SQL> select thread#,status,enabled from v$thread;
THREAD# STATUS ENABLED
---------- ------------------ ------------------------
1 CLOSED PUBLIC
7、再次修改pfile加入隐含参数“_no_recovery_through_resetlogs”
pga_aggregate_target=4g
sga_target=18g
sga_max_size=18g
*.audit_trail='none'
*.control_files='/oradata/control01.ctl'
*.db_files=1000
*.db_name='zjedb'
*.open_cursors=300
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
compatible='11.2.0.4'
_allow_resetlogs_corruption= TRUE-->增加此行
_no_recovery_through_resetlogs=TRUE-->增加此行
8、再次打开数据库
SQL> alter database open resetlogs;
Database altered.
这次open成功
9、去掉隐含参数，已正常方式启动
然把pfile中2个隐含参数去掉，再次重启数据库
_allow_resetlogs_corruption= TRUE
_no_recovery_through_resetlogs=TRUE
SQL> startup
ORACLE instance started.
Total System Global Area 1.9241E+10 bytes
Fixed Size 2261368 bytes
Variable Size 2483031688 bytes
Database Buffers 1.6710E+10 bytes
Redo Buffers 45658112 bytes
Database mounted.
Database opened.
启动正常
10、增加临时表空间
SQL> alter tablespace temp add tempfile '/oradata/temp1.dbf' size 2g autoextend on next 200m;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/oradata/temp1.dbf
11、再次检查控制文件与数据文件scn
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;
FILE# TO_CHAR(CHECKPOINT_CHANGE#,'99999999999
---------- ---------------------------------------
1 1457899201
2 1457899201
3 1457899201
4 1457899201
5 1457899201
6 1457899201
7 1457899201
8 1457899201
9 1457899201
10 1457899201
11 1457899201
12 1457899201
13 1457899201
14 1457899201
15 1457899201
16 1457899201
17 1457899201
18 1457899201
19 1457899201
20 1457899201
21 1457899201
21 rows selected.
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;
FILE# TO_CHAR(CHECKPOINT_CHANGE#,'99999999999
---------- ---------------------------------------
1 1457899201
2 1457899201
3 1457899201
4 1457899201
5 1457899201
6 1457899201
7 1457899201
8 1457899201
9 1457899201
10 1457899201
11 1457899201
12 1457899201
13 1457899201
14 1457899201
15 1457899201
16 1457899201
17 1457899201
18 1457899201
19 1457899201
20 1457899201
21 1457899201
21 rows selected.
SQL> select CHECKPOINT_CHANGE#,count(*) from V$DATAFILE_HEADER group by CHECKPOINT_CHANGE#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ----------
1457899201 21
控制文件与数据文件scn均一致
二、文献
1、RMAN Duplicate from RAC backup fails ORA-38856 (文档 ID 334899.1)
In this Document
Symptoms
Changes
Cause
Solution
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
RMAN duplicate using a backup taken from a RAC installation fails on opening the clone instance with
RMAN-06136: ORACLE error from auxiliary database:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
The target database is a RAC two node database being duplicated to a single instance database.
CHANGES
RMAN is being used to clone from a source backup taken from a RAC database.
CAUSE
Unpublished Bugs 4355382 and 11076718
ORA-38856 is the expected error during open database resetlogs when the set of enabled instances (redo threads) in the controlfile does not match the set of enabled instances (redo threads) in datafile checkpoint. This is expected behavior in a normal RAC restore/recover/open resetlogs situation.
However, in a cloning scenario where, once the clone database is opened with resetlogs you would never expect to restore the old (RAC) backup , this check doesnt matter.
SOLUTION
Apply fix for bug.
As a workaround you could do the following:
1.Set the following parameter in the auxiliary init.ora file:
_no_recovery_through_resetlogs=TRUE
Then open with resetlogs;
Once the auxiliary is opened, remove this hidden parameter.
2、ALTER DATABASE OPEN RESETLOGS fails with ORA-00392 (文档 ID 1352133.1)
SYMPTOMS
After a RESTORE/RECOVER for cloning a database, the following error appears while open the clone database:
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/<PATH>/group_1.log'
ORA-00312: online log 1 thread 1: '/<PATH>/group_12.log'
CHANGES
Cloned a database by RESTORE/RECOVER.
CAUSE
The first command "alter database open resetlogs" is abnormally abrupted leaving the redo log status as CLEARING/CLEARING_CURRENT in controlfile :
SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log order by first_change# ;
GROUP# THREAD# SEQUENCE# MEMBERS ARCHIVED STATUS FIRST_CHANGE#
------------- ----------- ----------- -------------- ---------- ---------------- -------------------
2 1 0 2 YES CLEARING 12142264718335
3 1 0 2 YES CLEARING 12142306351551
1 1 0 2 NO CLEARING_CURRENT 12142306351562
3 rows selected.
SOLUTION
At first, see if below command helps :
alter database clear unarchived logfile group 1 ;
alter database clear unarchived logfile group 2 ;
alter database clear unarchived logfile group 3 ;
alter database open resetlogs;
ELSE,
Recreate the controflile with the RESETLOGS option.
1) Get the controlfile trace from below command :
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/control.sql' resetlogs ;
2) Modify the CREATE CONTROLFILE script /tmp/control.sql and ensure that all directories for the online redo logs exist and Oracle has permission to write to it
3) Create the controlfile in NOMOUNT state :
SQL> STARTUP FORCE NOMOUNT
SQL> @/tmp/control.sql
controlfile created
4) Run a fake recovery :
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;
Type <CANCEL> when prompted
5) Open with RESETLOGS option:
SQL> ALTER DATABASE OPEN RESETLOGS ;
扫一扫
5182
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
