RAC 운영중 CLONEDB이용해서 무정지복구할때 undotbs issue

RAC를 운영하게 되면 각 노드마다의 undo tablespace가 따로따로 여러개 지정되어있습니다.

하지만, clone db는 single이기떄문에 undo tablespace를 하나만 복원해서 복구를 하게 되어도 되지 않을까 해서
하나만 복원하고 다른 undo tablespace는 복원하지 않게되면, 평상시 undo tablespace 가 에러나서 db가 open 되지 않는것과
같은 에러가 나면서 DB가 열리지 않습니다. 

아무리 undo_tablespace 가 하나만 지정되어있다고 하더라도, undo는 모두 복원해야 복구 후에 DB를 열 수 있습니다.

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      MANUAL

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1  << 하지만 DB를 open할때에는 모든 노드의 undo가 필요합니다.

예를 보시죠.

SQL> recover database until cancel;
ORA-00279: change 308389 generated at 03/10/2012 21:31:29 needed for thread 1
ORA-00289: suggestion : /home/oracle/archive/1_2_777590732.dbf
ORA-00280: change 308389 for thread 1 is in sequence #2


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/home/oracle/archive/1_2_777590732.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '/home/oracle/archive/1_2_777590732.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/home/oracle/cl/raw3_system.dbf'


SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

원래 백업에서는 undotbs1, undotbs2 둘다 있었는데 그 중 하나만 복원해서 복구한 경우입니다.
DB가 open 되지 않죠.
ORA-01092: ORACLE instance terminated.  << 전형적인 UNDO 관련 오류메시지

그래서 파일복사를 새로 하고 다시 복구시도를 해봤습니다.

[oracle@rac2 cl]$ rm *.dbf *.l control.ctl
[oracle@rac2 cl]$ vi re.sql
[oracle@rac2 cl]$ cp -v ~/backup/*.dbf .
`/home/oracle/backup/raw15_users.dbf' -> `./raw15_users.dbf'
`/home/oracle/backup/raw17_undotbs1.dbf' -> `./raw17_undotbs1.dbf'
`/home/oracle/backup/raw18_undotbs2.dbf' -> `./raw18_undotbs2.dbf'
`/home/oracle/backup/raw20_ts_new.dbf' -> `./raw20_ts_new.dbf'
`/home/oracle/backup/raw3_system.dbf' -> `./raw3_system.dbf'
`/home/oracle/backup/raw4_sysaux.dbf' -> `./raw4_sysaux.dbf'
[oracle@rac2 cl]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 10 21:41:56 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> @re
ORACLE instance started.

Total System Global Area  184549376 bytes
Fixed Size                  1272696 bytes
Variable Size              83887240 bytes
Database Buffers           96468992 bytes
Redo Buffers                2920448 bytes

Control file created.

SQL> recover database until time '2012-03-10:20:10:37' using backup controlfile;
ORA-00279: change 288267 generated at 03/10/2012 20:08:06 needed for thread 2
ORA-00289: suggestion : /home/oracle/archive/2_23_777548049.dbf
ORA-00280: change 288267 for thread 2 is in sequence #23


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
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


SQL> alter system set "_no_recovery_through_resetlogs"=TRUE;

System altered.

SQL> alter database open resetlogs;

Database altered.

제대로 열렸습니다! 3시간의 삽질이 열매를 맺었네요.