Test Version : 12.1.0.2.0
Fils system : ASM
Engine : Oracle Enterprise / Grid
OS : CentOS 6.7
0. ASM 으로 구성 되있는 디비에 컨트롤 파일이 손상이 발생 , 미러링 파일을 이용해 복구를 하는 과정 입니다.
1. 컨트롤 파일 손상을 발견
alter database open
Mon Jul 15 13:57:03 2019
WARNING: Read Failed. group:1 disk:0 AU:63 offset:131072 size:131072
path:/dev/oracleasm/disks/DATA1
incarnation:0x0 synchronous result:'I/O error'
subsys:System krq:0x7f6b11fb3578 bufp:0x7f6b119e7e00 osderr1:0x69b5 osderr2:0x0
IO elapsed time: 0 usec Time waited on I/O: 0 usec
WARNING: failed to read mirror side 1 of virtual extent 1 logical extent 0 of file 257 in group [1.649683322] from disk DATA_0000 allocation unit 63 reason error; if possible, will try another mirror side
Mon Jul 15 13:57:03 2019
Errors in file /home/oracle/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_6298.trc:
ORA-00202: control file: '+DATA/TESTDB/CONTROLFILE/current.257.996584973'
ORA-15081: failed to submit an I/O operation to a disk
Mon Jul 15 13:57:03 2019
Errors in file /home/oracle/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_6298.trc:
ORA-00204: error in reading (block 23, # blocks 64) of control file
ORA-00202: control file: '+DATA/TESTDB/CONTROLFILE/current.257.996584973'
ORA-15081: failed to submit an I/O operation to a disk
ORA-204 signalled during: alter database open...
Mon Jul 15 14:01:29 2019
Mon Jul 15 13:57:03 2019
WARNING: Read Failed. group:1 disk:0 AU:63 offset:131072 size:131072
path:/dev/oracleasm/disks/DATA1
incarnation:0x0 synchronous result:'I/O error'
subsys:System krq:0x7f6b11fb3578 bufp:0x7f6b119e7e00 osderr1:0x69b5 osderr2:0x0
IO elapsed time: 0 usec Time waited on I/O: 0 usec
WARNING: failed to read mirror side 1 of virtual extent 1 logical extent 0 of file 257 in group [1.649683322] from disk DATA_0000 allocation unit 63 reason error; if possible, will try another mirror side
Mon Jul 15 13:57:03 2019
Errors in file /home/oracle/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_6298.trc:
ORA-00202: control file: '+DATA/TESTDB/CONTROLFILE/current.257.996584973'
ORA-15081: failed to submit an I/O operation to a disk
Mon Jul 15 13:57:03 2019
Errors in file /home/oracle/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_6298.trc:
ORA-00204: error in reading (block 23, # blocks 64) of control file
ORA-00202: control file: '+DATA/TESTDB/CONTROLFILE/current.257.996584973'
ORA-15081: failed to submit an I/O operation to a disk
ORA-204 signalled during: alter database open...
Mon Jul 15 14:01:29 2019
2. 미러링 컨트롤 파일을 이용해 오픈 시도 정상 유무 확인
sys@testdb>
sys@testdb> alter system set control_files = '+FRG/TESTDB/CONTROLFILE/current.256.902932949' scope=spfile;
<< spfile 에 미러링 파일만 인식 하도록 값을 변경
System altered.
sys@testdb>
sys@testdb>
sys@testdb> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
sys@testdb> startup;
## 로그 파일 내용 ##
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0
System name: Linux
Node name: testora.localdomain
Release: 2.6.32-573.el6.x86_64
Version: #1 SMP Thu Jul 23 15:44:03 UTC 2015
Machine: x86_64
Using parameter settings in server-side pfile /home/oracle/app/oracle/product/12.1.0/dbs/inittestdb.ora
System parameters with non-default values:
processes = 300
spfile = "+DATA/testdb/PARAMETERFILE/spfile.269.902946971"
nls_language = "AMERICAN"
nls_territory = "AMERICA"
sga_target = 872M
control_files = "+FRG/TESTDB/CONTROLFILE/current.256.902932949"
db_block_size = 8192
compatible = "12.1.0.2.0"
db_create_file_dest = "+DATA"
db_recovery_file_dest = "+FRG"
db_recovery_file_dest_size= 4815M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=testdbXDB)"
local_listener = "LISTENER_TESTDB"
utl_file_dir = "/tmp"
parallel_min_servers = 4
audit_file_dest = "/home/oracle/app/oracle/admin/testdb/adump"
audit_trail = "NONE"
db_name = "testdb"
open_cursors = 300
pga_aggregate_target = 290M
diagnostic_dest = "/home/oracle/app/oracle"
enable_pluggable_database= TRUE
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Starting background process PMON
Mon Jul 15 14:02:30 2019
PMON started with pid=2, OS id=6569
.
.
.
Starting background process CJQ0
Completed: alter database open
Mon Jul 15 14:03:52 2019
CJQ0 started with pid=47, OS id=6894
Mon Jul 15 14:04:08 2019
db_recovery_file_dest_size of 4815 MB is 3.57% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Jul 15 14:04:18 2019
sys@testdb> alter system set control_files = '+FRG/TESTDB/CONTROLFILE/current.256.902932949' scope=spfile;
<< spfile 에 미러링 파일만 인식 하도록 값을 변경
System altered.
sys@testdb>
sys@testdb>
sys@testdb> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
sys@testdb> startup;
## 로그 파일 내용 ##
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0
System name: Linux
Node name: testora.localdomain
Release: 2.6.32-573.el6.x86_64
Version: #1 SMP Thu Jul 23 15:44:03 UTC 2015
Machine: x86_64
Using parameter settings in server-side pfile /home/oracle/app/oracle/product/12.1.0/dbs/inittestdb.ora
System parameters with non-default values:
processes = 300
spfile = "+DATA/testdb/PARAMETERFILE/spfile.269.902946971"
nls_language = "AMERICAN"
nls_territory = "AMERICA"
sga_target = 872M
control_files = "+FRG/TESTDB/CONTROLFILE/current.256.902932949"
db_block_size = 8192
compatible = "12.1.0.2.0"
db_create_file_dest = "+DATA"
db_recovery_file_dest = "+FRG"
db_recovery_file_dest_size= 4815M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=testdbXDB)"
local_listener = "LISTENER_TESTDB"
utl_file_dir = "/tmp"
parallel_min_servers = 4
audit_file_dest = "/home/oracle/app/oracle/admin/testdb/adump"
audit_trail = "NONE"
db_name = "testdb"
open_cursors = 300
pga_aggregate_target = 290M
diagnostic_dest = "/home/oracle/app/oracle"
enable_pluggable_database= TRUE
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Starting background process PMON
Mon Jul 15 14:02:30 2019
PMON started with pid=2, OS id=6569
.
.
.
Starting background process CJQ0
Completed: alter database open
Mon Jul 15 14:03:52 2019
CJQ0 started with pid=47, OS id=6894
Mon Jul 15 14:04:08 2019
db_recovery_file_dest_size of 4815 MB is 3.57% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Jul 15 14:04:18 2019
3. 컨트롤 파일 복사를 위해 디비 종료
sys@testdb>
sys@testdb> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
sys@testdb> startup mount;
ORACLE instance started.
sys@testdb> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
sys@testdb> startup mount;
ORACLE instance started.
4. 에러가 발생한 컨트롤 파일 삭제
ASMCMD>
ASMCMD> cd testdb
ASMCMD> ls
2AEC347502BE0A03E053B4AB2134F623/
2B011A2FEEAC384EE053B4AB213417DA/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> cd controlfile
ASMCMD> ls
current.257.996584973
ASMCMD> rm current.257.996584973 << 에러가 발생한 파일을 삭제
ASMCMD> ls -l
ASMCMD-8002: entry 'controlfile' does not exist in directory '+data/testdb/'
ASMCMD> ls
ASMCMD-8002: entry 'controlfile' does not exist in directory '+data/testdb/'
ASMCMD> cd ..
ls
ASMCMD> 2AEC347502BE0A03E053B4AB2134F623/
2B011A2FEEAC384EE053B4AB213417DA/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD>
ASMCMD>
ASMCMD>
ASMCMD> ls -l
ASMCMD> cd testdb
ASMCMD> ls
2AEC347502BE0A03E053B4AB2134F623/
2B011A2FEEAC384EE053B4AB213417DA/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> cd controlfile
ASMCMD> ls
current.257.996584973
ASMCMD> rm current.257.996584973 << 에러가 발생한 파일을 삭제
ASMCMD> ls -l
ASMCMD-8002: entry 'controlfile' does not exist in directory '+data/testdb/'
ASMCMD> ls
ASMCMD-8002: entry 'controlfile' does not exist in directory '+data/testdb/'
ASMCMD> cd ..
ls
ASMCMD> 2AEC347502BE0A03E053B4AB2134F623/
2B011A2FEEAC384EE053B4AB213417DA/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD>
ASMCMD>
ASMCMD>
ASMCMD> ls -l
5. Rman 으로 컨트롤 복구
testora.localdomain@oracle:testdb:/home/oracle> rman target=/
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jul 15 14:14:02 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN>
RMAN> startup nomount; << Rman 에서 ASM 에 접근 하기 위해 디비를 nomount 단계로 올린다.
Oracle instance started
Total System Global Area 914358272 bytes
Fixed Size 2930800 bytes
Variable Size 352323472 bytes
Database Buffers 553648128 bytes
Redo Buffers 5455872 bytes
RMAN> restore controlfile to '+DATA' from '+FRG/TESTDB/CONTROLFILE/current.256.902932949';
<< 미러링 파일을 이용해 복구
Starting restore at 15-JUL-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=355 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 15-JUL-19
RMAN>
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jul 15 14:14:02 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN>
RMAN> startup nomount; << Rman 에서 ASM 에 접근 하기 위해 디비를 nomount 단계로 올린다.
Oracle instance started
Total System Global Area 914358272 bytes
Fixed Size 2930800 bytes
Variable Size 352323472 bytes
Database Buffers 553648128 bytes
Redo Buffers 5455872 bytes
RMAN> restore controlfile to '+DATA' from '+FRG/TESTDB/CONTROLFILE/current.256.902932949';
<< 미러링 파일을 이용해 복구
Starting restore at 15-JUL-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=355 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 15-JUL-19
RMAN>
### ASM 에서 복사된 컨트롤 파일 확인
ASMCMD>
ASMCMD>
ASMCMD> cd testdb
ASMCMD-8002: entry 'testdb' does not exist in directory '+data/testdb/'
ASMCMD> ls
2AEC347502BE0A03E053B4AB2134F623/
2B011A2FEEAC384EE053B4AB213417DA/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> cd controlfile
ASMCMD> ls
current.257.1013696153
ASMCMD> ls -l
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE JUL 15 14:00:00 Y current.257.1013696153
ASMCMD>
ASMCMD>
ASMCMD>
ASMCMD> cd testdb
ASMCMD-8002: entry 'testdb' does not exist in directory '+data/testdb/'
ASMCMD> ls
2AEC347502BE0A03E053B4AB2134F623/
2B011A2FEEAC384EE053B4AB213417DA/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> cd controlfile
ASMCMD> ls
current.257.1013696153
ASMCMD> ls -l
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE JUL 15 14:00:00 Y current.257.1013696153
ASMCMD>
### asm to filesystem 컨트롤 파일 카피
RMAN> restore controlfile to '/home/oracle/app/oracle/datafile/controlfile/control03.ctl' from '+FRG/TESTDB/CONTROLFILE/current.256.902932949';
Starting restore at 15-JUL-19
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
Finished restore at 15-JUL-19
RMAN>
## filesystem 에서 정상 복구 됐는지 확인
testora.localdomain@oracle:testdb:/home/oracle/app/oracle/datafile/controlfile> ls -l
⑷ 17584
-rw-r-----. 1 oracle dba 18006016 2019-07-15 14:36 control03.ctl
You have mail in /var/spool/mail/oracle
testora.localdomain@oracle:testdb:/home/oracle/app/oracle/datafile/controlfile>
RMAN> restore controlfile to '/home/oracle/app/oracle/datafile/controlfile/control03.ctl' from '+FRG/TESTDB/CONTROLFILE/current.256.902932949';
Starting restore at 15-JUL-19
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
Finished restore at 15-JUL-19
RMAN>
## filesystem 에서 정상 복구 됐는지 확인
testora.localdomain@oracle:testdb:/home/oracle/app/oracle/datafile/controlfile> ls -l
⑷ 17584
-rw-r-----. 1 oracle dba 18006016 2019-07-15 14:36 control03.ctl
You have mail in /var/spool/mail/oracle
testora.localdomain@oracle:testdb:/home/oracle/app/oracle/datafile/controlfile>
6. 복구 된 컨트롤 파일 spfile 에 적용
alter system set control_files = '+DATA/TESTDB/CONTROLFILE/current.257.1013696153','+FRG/TESTDB/CONTROLFILE/current.256.902932949','/home/oracle/app/oracle/datafile/controlfile/control03.ctl' scope=spfile;
7. 정상 오픈 확인
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0
System name:Linux
Node name:testora.localdomain
Release:2.6.32-573.el6.x86_64
Version:#1 SMP Thu Jul 23 15:44:03 UTC 2015
Machine:x86_64
Using parameter settings in server-side pfile /home/oracle/app/oracle/product/12.1.0/dbs/inittestdb.ora
System parameters with non-default values:
processes = 300
spfile = "+DATA/testdb/PARAMETERFILE/spfile.269.902946971"
nls_language = "AMERICAN"
nls_territory = "AMERICA"
sga_target = 872M
control_files = "+DATA/TESTDB/CONTROLFILE/current.257.1013696153"
control_files = "+FRG/TESTDB/CONTROLFILE/current.256.902932949"
control_files = "/home/oracle/app/oracle/datafile/controlfile/control03.ctl" < 파일 시스템 복구도 적용 확인
db_block_size = 8192
compatible = "12.1.0.2.0"
db_create_file_dest = "+DATA"
db_recovery_file_dest = "+FRG"
db_recovery_file_dest_size= 4815M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=testdbXDB)"
local_listener = "LISTENER_TESTDB"
utl_file_dir = "/tmp"
parallel_min_servers = 4
audit_file_dest = "/home/oracle/app/oracle/admin/testdb/adump"
audit_trail = "NONE"
db_name = "testdb"
open_cursors = 300
pga_aggregate_target = 290M
diagnostic_dest = "/home/oracle/app/oracle"
enable_pluggable_database= TRUE
NOTE: remote asm mode is local (mode 0x1; from cluster type)
2019-07-15 14:41:33.545000 +09:00
Starting background process PMON
PMON started with pid=2, OS id=8234
Starting background process PSP0
PSP0 started with pid=3, OS id=8236
Starting background process VKTM
2019-07-15 14:41:34.629000 +09:00
VKTM started with pid=4, OS id=8238 at elevated (RT) priority
Starting background process GEN0
VKTM running at (1)millisec precision with DBRM quantum (100)ms
GEN0 started with pid=5, OS id=8242
Starting background process MMAN
MMAN started with pid=6, OS id=8244
....
Successful mount of redo thread 1, with mount id 2796999978
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
2019-07-15 14:42:21.509000 +09:00
alter database open
Ping without log force is disabled
.
2019-07-15 14:42:22.667000 +09:00
Starting background process TMON
TMON started with pid=29, OS id=8337
Thread 1 opened at log sequence 8775
Current log# 3 seq# 8775 mem# 0: +DATA/TESTDB/ONLINELOG/group_3.260.902932961
Current log# 3 seq# 8775 mem# 1: +FRG/TESTDB/ONLINELOG/group_3.259.902932965
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Network Resource Management enabled for Process LG00 (pid 8262) for Exadata I/O
SMON: enabling cache recovery
2019-07-15 14:42:26.235000 +09:00
[8314] Successfully onlined Undo Tablespace 2.
Network Resource Management enabled for Process LG01 (pid 8266) for Exadata I/O
Undo initialization finished serial:0 start:7462494 end:7464324 diff:1830 ms (1.8 seconds)
Verifying minimum file header compatibility (11g) for tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Starting background process SMCO
SMCO started with pid=33, OS id=8341
Database Characterset is KO16KSC5601
2019-07-15 14:42:27.702000 +09:00
No Resource Manager plan active
2019-07-15 14:42:30.516000 +09:00
replication_dependency_tracking turned off (no async multimaster replication found)
2019-07-15 14:42:31.968000 +09:00
Starting background process AQPC
AQPC started with pid=36, OS id=8347
2019-07-15 14:42:36.723000 +09:00
Database Characterset for PDB$SEED is KO16KSC5601
2019-07-15 14:42:40.817000 +09:00
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 3
2
2019-07-15 14:42:47.696000 +09:00
Opening pdb PDB$SEED (2) with no Resource Manager plan active
2019-07-15 14:42:51.905000 +09:00
alter pluggable database all open
2019-07-15 14:42:56.000000 +09:00
Database Characterset for PDB1 is KO16KSC5601
2019-07-15 14:43:02.649000 +09:00
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 3
2
2019-07-15 14:43:10.201000 +09:00
Shared IO Pool defaulting to 36MB. Trying to get it from Buffer Cache for process 8278.
2019-07-15 14:43:11.748000 +09:00
Opening pdb PDB1 (3) with no Resource Manager plan active
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
2019-07-15 14:43:12.966000 +09:00
Pluggable database PDB1 opened read write
Completed: alter pluggable database all open
Starting background process CJQ0
CJQ0 started with pid=37, OS id=8517
Completed: alter database open
2019-07-15 14:43:34.610000 +09:00
참조 문서 : How to copy control file in ASM (문서 ID 2006213.1)
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
9 | DB 컨트롤 파일 추가하기 | 명품관 | 2023.03.27 | 950 |
8 | data pump 모니터링 | 명품관 | 2023.01.31 | 958 |
7 | Rman 복구 중 Netbackup 이 연결이 안되는 에러 [1] | Talros | 2022.11.24 | 118 |
6 | 12C RAC, OS Fault 로 다운된 1번 Node 재구성 | Talros | 2020.08.28 | 1093 |
5 | alter database begin backup 사용을 권해 드립니다. [1] | 우뽕 | 2020.01.22 | 14299 |
» | Rman 을 이용해 ASM 에 컨트롤 파일 복구 하기 [1] | Talros | 2019.07.16 | 2850 |
3 | Data Pump Query 파라미터 사용법 [1] | Talros | 2016.01.28 | 4484 |
2 | Data pump 사용시 Warnings Like DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled | Talros | 2016.01.08 | 1955 |
1 | 비정상적인 복구를 수행하게 되는 경우(Redo log 유실, Archive log 파일 부재, Resetlog open 에러) | 명품관 | 2015.12.22 | 6738 |
좋은 글 감사합니다.