RMAN을 이용한 control file 복구

2013.02.18 20:15

** RMAN을 이용한 DB 복구 ***

==> 상황 : 디스크 전체가 fault 되어 DB 기동이 불가능한 상황 (RAC 구성)


[BEGIN] 2013-02-18 오후 4:57:27


su - oracle
[ORACLE_SID = rac1 ]

rac1:oracle/ > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 18 02:18:00 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.



==>> DB startup 실패

SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/rac/spfilerac.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/rac/spfilerac.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +DATA/rac/spfilerac.ora
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-06512: at line 4
SQL> exit
Disconnected


==> DATA diskgroup상태가 OFFLINE임

rac1:oracle/ > crs_stat -t      
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.DATA.dg    ora....up.type OFFLINE   OFFLINE               
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora.OCRVOT.dg  ora....up.type ONLINE    ONLINE    rac1        
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1        
ora.cvu        ora.cvu.type   ONLINE    ONLINE    rac1        
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               
ora....network ora....rk.type ONLINE    ONLINE    rac1        
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    rac1        
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1        
ora.rac.db     ora....se.type OFFLINE   OFFLINE               
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    OFFLINE   OFFLINE               
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    OFFLINE   OFFLINE               
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2        
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac1

        
==> DATA diskgroup 재기동 시도 / 실패 

rac1:oracle/ > srvctl start diskgroup -g DATA        
 
PRCR-1079 : Failed to start resource ora.DATA.dg
CRS-5017: The resource action "ora.DATA.dg start" encountered the following error: 
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA"
. For details refer to "(:CLSN00107:)" in "/grid/product/11.2.0/crs/log/rac1/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.DATA.dg' on 'rac1' failed
CRS-5017: The resource action "ora.DATA.dg start" encountered the following error: 
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA"
. For details refer to "(:CLSN00107:)" in "/grid/product/11.2.0/crs/log/rac2/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.DATA.dg' on 'rac2' failed


rac1:oracle/ > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    OFFLINE               
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora.OCRVOT.dg  ora....up.type ONLINE    ONLINE    rac1        
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1        
ora.cvu        ora.cvu.type   ONLINE    ONLINE    rac1        
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               
ora....network ora....rk.type ONLINE    ONLINE    rac1        
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    rac1        
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1        
ora.rac.db     ora....se.type OFFLINE   OFFLINE               
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    OFFLINE   OFFLINE               
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    OFFLINE   OFFLINE               
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2        
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac1        



==>> DATA diskgroup 중지시킴

rac1:oracle/ > srvctl stop diskgroup -g DATA                   



 ==>> 이미 에러난 디스크 그룹 전체 삭제 

rac1:oracle/ > srvctl remove diskgroup -g DATA                
PRCA-1002 : Failed to remove CRS resource ora.DATA.dg for ASM Disk Group DATA
PRCR-1028 : Failed to remove resource ora.DATA.dg
PRCR-1072 : Failed to unregister resource ora.DATA.dg
CRS-0222: Resource 'ora.DATA.dg' has dependency error.



==>> -f 옵션으로 강제 삭제

rac1:oracle/ > srvctl remove diskgroup -g DATA -f                  



  ==>> 디스크그룹 삭제 되었음.

rac1:oracle/ > crs_stat -t                               
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora.OCRVOT.dg  ora....up.type ONLINE    ONLINE    rac1        
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1        
ora.cvu        ora.cvu.type   ONLINE    ONLINE    rac1        
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               
ora....network ora....rk.type ONLINE    ONLINE    rac1        
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    rac1        
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1        
ora.rac.db     ora....se.type OFFLINE   OFFLINE               
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    OFFLINE   OFFLINE               
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    OFFLINE   OFFLINE               
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2        
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac1        
rac1:oracle/ > 
rac1:oracle/ > asm



==>> sysasm으로 sqlplus 접속

rac1:oracle/ > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 18 02:18:51 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> conn / as sysasm                           
Connected.



==>> 디스크장애 해결했다 치고 디스크그룹 재작성


SQL> create diskgroup DATA external redundancy disk '/dev/raw/raw5';  
Diskgroup created.



SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options


==>> DATA diskgroup 재기동 되었음


rac1:oracle/ > crs_stat -t                            
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    ONLINE    rac1        
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac1        
ora.OCRVOT.dg  ora....up.type ONLINE    ONLINE    rac1        
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1        
ora.cvu        ora.cvu.type   ONLINE    ONLINE    rac1        
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               
ora....network ora....rk.type ONLINE    ONLINE    rac1        
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    rac1        
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1        
ora.rac.db     ora....se.type OFFLINE   OFFLINE               
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    OFFLINE   OFFLINE               
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    OFFLINE   OFFLINE               
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2        
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac1        
rac1:oracle/ > 


 ==> 백업용으로 만들어진 pfile 확인
     (통상 $ORACLE_BASE/dbname/pfile 아래 있음)

rac1:oracle/ > cd /oracle/product/11.2.0/admin/rac/pfile/
     
rac1:pfile/ > ls
init.ora.112201322538



rac1:pfile/ > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 18 02:21:06 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.
 

==> 백업용 pfile로 강제로 nomount까지 DB 기동
 
SQL> startup nomount pfile=init.ora.112201322538      
ORACLE instance started.

Total System Global Area 2505338880 bytes
Fixed Size    2230952 bytes
Variable Size  620758360 bytes
Database Buffers 1862270976 bytes
Redo Buffers   20078592 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options



==> 평소 RMAN으로 백업받은 디렉토리 확인

rac1:pfile/ > cd /ocfs/RMAN/          
rac1:RMAN/ > ls -lrt
total 538916
-rw-r----- 1 oracle dba 477519872 Feb 18 01:21 05o2868c_1_1
-rw-r----- 1 oracle dba  18579456 Feb 18 01:21 c-2395209945-20130218-02
-rw-r----- 1 oracle dba     11776 Feb 18 01:21 07o28699_1_1
-rw-r----- 1 oracle dba  18579456 Feb 18 01:21 c-2395209945-20130218-03
-rw-r----- 1 oracle dba  18579456 Feb 18 01:53 c-2395209945-20130218-04
-rw-r----- 1 oracle dba  18579456 Feb 18 02:16 c-2395209945-20130218-05


==>> RMAN 시작
 
rac1:RMAN/ > rman target /           

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 18 02:21:34 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RAC (not mounted)



=>> RMAN으로 백업셋으로부터 콘트롤파일 백업시도
(백업셋 중에서 c-로 시작하는 파일이 콘트롤 파일백업임) 

RMAN> restore controlfile from '/ocfs/RMAN/c-2395209945-20130218-05';                             
 
                                                                                            

Starting restore at 18-FEB-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=202 instance=rac1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/rac/controlfile/current.256.807675715
Finished restore at 18-FEB-13



==>> DB 마운트 시킴

RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1


==> spfile 복구

RMAN> restore spfile from autobackup;

Starting restore at 18-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=202 instance=rac1 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130218
channel ORA_DISK_1: AUTOBACKUP found: /ocfs/RMAN/c-2395209945-20130218-05
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /ocfs/RMAN/c-2395209945-20130218-05
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 18-FEB-13

RMAN> exit


Recovery Manager complete.

 
==>> spfile 복구 후 DB 셧다운 후 nomount 까지 재기동
 
rac1:RMAN/ > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 18 02:22:46 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 2505338880 bytes
Fixed Size    2230952 bytes
Variable Size  620758360 bytes
Database Buffers 1862270976 bytes
Redo Buffers   20078592 bytes


==>> spfile 복구된거 확인

SQL> show parameter spfile;
NAME     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
spfile     string
+DATA/rac/spfilerac.ora
SQL> 


==>> DB 마운트 시키기(콘트롤 파일 때문에 에러남)

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options



==>> RMAN 재기동 후 콘트롤 파일 restore

rac1:RMAN/ > rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 18 02:23:48 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RAC (not mounted)

RMAN> restore controlfile from '/ocfs/RMAN/c-2395209945-20130218-05';

Starting restore at 18-FEB-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=396 instance=rac1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/rac/controlfile/current.258.807675851
Finished restore at 18-FEB-13


==>> DB mount

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1


==>> RMAN에서 list backup 조회 가능

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    455.39M    DISK        00:00:14     18-FEB-13      
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20130218T012115
        Piece Name: /ocfs/RMAN/05o2868c_1_1
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 275348     18-FEB-13 +DATA/rac/datafile/system.259.807675121
  2       Full 275348     18-FEB-13 +DATA/rac/datafile/sysaux.260.807675121
  3       Full 275348     18-FEB-13 +DATA/rac/datafile/undotbs1.261.807675121
  4       Full 275348     18-FEB-13 +DATA/rac/datafile/undotbs2.262.807675121
  5       Full 275348     18-FEB-13 +DATA/rac/datafile/users.263.807675121

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    17.70M     DISK        00:00:01     18-FEB-13      
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20130218T012131
        Piece Name: /ocfs/RMAN/c-2395209945-20130218-02
  SPFILE Included: Modification time: 18-FEB-13
  SPFILE db_unique_name: RAC
  Control File Included: Ckp SCN: 275375       Ckp time: 18-FEB-13

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       11.00K     DISK        00:00:00     18-FEB-13      
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20130218T012145
        Piece Name: /ocfs/RMAN/07o28699_1_1

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    7       275010     18-FEB-13 275391     18-FEB-13
  2    4       275007     18-FEB-13 275395     18-FEB-13



==>> RMAN에서 restore database

RMAN> restore database;

Starting restore at 18-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=396 instance=rac1 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/rac/datafile/system.259.807675121
channel ORA_DISK_1: restoring datafile 00002 to +DATA/rac/datafile/sysaux.260.807675121
channel ORA_DISK_1: restoring datafile 00003 to +DATA/rac/datafile/undotbs1.261.807675121
channel ORA_DISK_1: restoring datafile 00004 to +DATA/rac/datafile/undotbs2.262.807675121
channel ORA_DISK_1: restoring datafile 00005 to +DATA/rac/datafile/users.263.807675121
channel ORA_DISK_1: reading from backup piece /ocfs/RMAN/05o2868c_1_1
channel ORA_DISK_1: piece handle=/ocfs/RMAN/05o2868c_1_1 tag=TAG20130218T012115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 18-FEB-13



==>> RMAN에서 recover database(에러생김)

RMAN> recover database;
Starting recover at 18-FEB-13
using channel ORA_DISK_1

starting media recovery

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/18/2013 02:25:25
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 2 with sequence 1 and starting SCN of 275349 found to restore

RMAN> exit


==>> SQLPLUS에서 database recover

Recovery Manager complete.
rac1:RMAN/ > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 18 02:25:46 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 275349 generated at 02/18/2013 02:16:00 needed for thread 1
ORA-00289: suggestion : +DATA
ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'RAC'
ORA-00280: change 275349 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.




==> DB open

SQL> alter database open resetlogs;
Database altered.

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
rac1 OPEN



==>> DB 셧다운 후 RAC로 다시 시작

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options



rac1:RMAN/ > srvctl start database -d rac
rac1:RMAN/ > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 18 02:28:29 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select instance_name, status from gv$instance;

INSTANCE_NAME STATUS
---------------- ------------
rac1 OPEN
rac2 OPEN

SQL> 
[END] 2013-02-18 오후 5:08:33


또루아빠 SQL(오라클,mysql) , ,