*********************
尝试修复坏块
*********************
run {blockrecover datafile 8 block 2494466;}
*********************
查询时仍然报错
*********************
SQL> select t.owner,t.table_name, count_rows(t.table_name,t.owner) as NUM_ROWS from all_tables t where owner in ('BXGZ','BXGZ_BB','FAUSER','FA_USER_ZH','GZ_CSTP','XBRL' ) and num_rows <> 0 order by t.owner, t.table_name;
select t.owner,t.table_name, count_rows(t.table_name,t.owner) as NUM_ROWS from all_tables t where owner in ('BXGZ','BXGZ_BB','FAUSER','FA_USER_ZH','GZ_CSTP','XBRL' ) and num_rows <> 0 order by t.owner, t.table_name
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 2494466)
ORA-01110: data file 8: '/fapdb/fabak/bxgz.272.977742545'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "FAUSER.COUNT_ROWS", line 14
ORA-06512: at line 1
select file_name,file_id,tablespace_name from dba_data_files where file_id=8;
LE_NAME FILE_ID TABLESPACE_NAME
------------------------------ ---------- ------------------------------
/fapdb/fabak/bxgz.272.977742545 8 BXGZ
*********************
查询表空间日志记录模式
*********************
SQL> select tablespace_name,logging,force_logging from dba_tablespaces;
TABLESPACE_NAME LOGGING FOR
------------------------------ --------- ---
SYSTEM LOGGING NO
SYSAUX LOGGING NO
UNDOTBS1 LOGGING NO
TEMP NOLOGGING NO
UNDOTBS2 LOGGING NO
USERS LOGGING NO
FAUSER LOGGING NO
BXGZ LOGGING NO
OGG LOGGING NO
XBRL LOGGING NO
XBRL_TEMP NOLOGGING NO
TABLESPACE_NAME LOGGING FOR
------------------------------ --------- ---
BXGZ_TEMP NOLOGGING NO
QDII_TEMP NOLOGGING NO
QDII LOGGING NO
14 rows selected.
SQL>
***************
主库参数文件
***************
fadb1.__db_cache_size=262529875968
fadb2.__db_cache_size=262529875968
fadb1.__java_pool_size=3758096384
fadb2.__java_pool_size=3758096384
fadb1.__large_pool_size=1610612736
fadb2.__large_pool_size=1610612736
fadb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
fadb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
fadb1.__pga_aggregate_target=75161927680
fadb2.__pga_aggregate_target=75161927680
fadb1.__sga_target=300647710720
fadb2.__sga_target=300647710720
fadb1.__shared_io_pool_size=0
fadb2.__shared_io_pool_size=0
fadb1.__shared_pool_size=30064771072
fadb2.__shared_pool_size=31138512896
fadb1.__streams_pool_size=1073741824
fadb2.__streams_pool_size=0
*._gc_policy_time=0
*._gc_undo_affinity=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/u01/app/oracle/admin/fadb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=31
*.control_files='+DATADG/fadb/controlfile/current.256.977591181'
*.db_block_size=8192
*.db_create_file_dest='+DATADG'
*.db_domain=''
*.db_file_name_convert='/fapdb/fabak/','+DATADG/fadb/datafile/'
*.db_name='fadb'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=fadbXDB)'
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
*.fal_client='FADB'
*.fal_server='FABAK'
fadb1.instance_number=1
fadb2.instance_number=2
*.log_archive_config='dg_config=(fadb,fabak)'
*.log_archive_dest_1='location=/faarch valid_for=(all_logfiles,all_roles) db_unique_name=fadb'
*.log_archive_dest_2='service=fabak lgwr async compression=enable valid_for=(online_logfiles,primary_role) db_unique_name=fabak'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/fapdb/fabak/','+DATADG/fadb/onlinelog'
*.max_dump_file_size='4096M'
*.open_cursors=300
*.parallel_force_local=TRUE
*.parallel_max_servers=288
*.pga_aggregate_target=75161927680
*.processes=2000
*.remote_listener='gzrac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.result_cache_max_size=0
*.sec_case_sensitive_logon=FALSE
*.sessions=2205
*.sga_target=300647710720
*.standby_archive_dest='location=/faarch'
*.standby_file_management='AUTO'
fadb2.thread=2
fadb1.thread=1
*.undo_retention=10800
fadb1.undo_tablespace='UNDOTBS1'
fadb2.undo_tablespace='UNDOTBS2'
*********************
判断因为主库因为没有开启force logging导致坏块
*********************
[gzrac1:oracle]:/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 23 16:23:34 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG NO
LE_NAME FILE_ID TABLESPACE_NAME
---------------------------------------- ---------- ------------------------------
+DATADG/fadb/datafile/bxgz.272.977742545 8 BXGZ
*********************
如果主库可以关库，可以考虑直接copy数据文件的方式，但是主库当前不能关闭
*********************
ASMCMD> cp SYSAUX.260.894187589 /home/grid/sysaux.dbf
copying +datadg01/xedb/datafile/SYSAUX.260.894187589 -> /home/grid/sysaux.dbf
*********************
查询坏块的分布情况
*********************
SELECT e.owner,
e.segment_type,
e.segment_name,
e.partition_name,
c.file#,
greatest(e.block_id, c.block#) corr_start_block#,
least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) corr_end_block#,
least(e.block_id + e.blocks - 1, c.block# + c.blocks - 1) -
greatest(e.block_id, c.block#) + 1 blocks_corrupted,
null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner,
s.segment_type,
s.segment_name,
s.partition_name,
c.file#,
header_block corr_start_block#,
header_block corr_end_block#,
1 blocks_corrupted,
'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner,
null segment_type,
null segment_name,
null partition_name,
c.file#,
greatest(f.block_id, c.block#) corr_start_block#,
least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) corr_end_block#,
least(f.block_id + f.blocks - 1, c.block# + c.blocks - 1) -
greatest(f.block_id, c.block#) + 1 blocks_corrupted,
'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ------------------ -------------------- ------------------------------ ---------- ----------------- --------------- ---------------- --------------
FA_USER_ZH TABLE S_TMP_DATA_QY_INFO 6 1467205 1467207 3
FA_USER_ZH INDEX PK_A001JJCBLRYB 6 1535084 1535087 4
FA_USER_ZH TABLE A001JJCBLRRB 6 1535091 1535095 5
FA_USER_ZH INDEX PK_A001JJCBLRRB 6 1535100 1535103 4
FA_USER_ZH INDEX INX1_EXDATA_OPENFUND 6 1537668 1537759 92
FA_USER_ZH INDEX INX1_EXDATA_OPENFUND 6 1537776 1537791 16
FA_USER_ZH INDEX INX1_EXDATA_OPENFUND 6 1537794 1537807 14
FA_USER_ZH INDEX INX1_EXDATA_OPENFUND 6 1537824 1537919 96
FA_USER_ZH INDEX INX1_EXDATA_OPENFUND 6 1538480 1538511 32
FA_USER_ZH INDEX INX1_EXDATA_OPENFUND 6 1538528 1538559 32
GZ_CSTP TABLE YH_PARTYACCOUNT 6 1539202 1539327 126
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ------------------ -------------------- ------------------------------ ---------- ----------------- --------------- ---------------- --------------
GZ_CSTP TABLE YH_PARTYACCOUNT 6 1539330 1539390 61
FA_USER_ZH TABLE TMP_JJHZHQ_ZZGZSJ2 6 1547233 1547239 7
FAUSER INDEX PK_A039JJHZGZBTEMP 6 1547240 1547247 8
6 1547249 1547263 15 Free Block
FAUSER TABLE A2018043FCWVCH 6 1547650 1547727 78
FAUSER TABLE A2018043FCWVCH 6 1547760 1547775 16
FAUSER TABLE A2018023FCWVCH 6 1547778 1547791 14
FAUSER TABLE A2018023FCWVCH 6 1547856 1547903 48
FAUSER INDEX PK_A2018033ZQXX 6 1547906 1548015 110
FAUSER TABLE A2018041ZQXX 6 1548162 1548271 110
FA_USER_ZH TABLE A2018JJHZHQ 6 1548292 1548303 12
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ------------------ -------------------- ------------------------------ ---------- ----------------- --------------- ---------------- --------------
FA_USER_ZH TABLE A2018JJHZHQ 6 1548305 1548415 111
FA_USER_ZH TABLE A2018JJHZHQ 6 1548418 1548543 126
FA_USER_ZH TABLE A2018JJHZHQ 6 1548546 1548671 126
FA_USER_ZH TABLE A2018JJHZHQ 6 1548674 1548799 126
FA_USER_ZH TABLE A2018JJHZHQ 6 1548802 1548927 126
FA_USER_ZH TABLE A2018JJHZHQ 6 1548930 1549055 126
FA_USER_ZH INDEX INX1_EXDATA_OPENFUND 7 1576096 1576159 64
FA_USER_ZH INDEX INX1_EXDATA_OPENFUND 7 1576322 1576447 126
FA_USER_ZH INDEX INX1_EXDATA_OPENFUND 7 1576464 1576575 112
FA_USER_ZH INDEX INX1_EXDATA_OPENFUND 7 1576578 1576703 126
FA_USER_ZH INDEX INX1_EXDATA_OPENFUND 7 1576706 1576831 126
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ------------------ -------------------- ------------------------------ ---------- ----------------- --------------- ---------------- --------------
FAUSER TABLE A2018003ZQXX 7 1582529 1582543 15
FAUSER TABLE A2018003ZQXX 7 1582545 1582559 15
FAUSER TABLE A2018003ZQXX 7 1582561 1582575 15
FAUSER TABLE T_YGZ_GZZZ_MX 7 1585156 1585247 92
FAUSER TABLE T_YGZ_GZZZ_MX 7 1585410 1585535 126
FAUSER TABLE T_YGZ_GZZZ_MX 7 1585538 1585663 126
FAUSER TABLE T_YGZ_GZZZ_MX 7 1585666 1585727 62
FAUSER TABLE T_YGZ_GZZZ_MX 7 1585794 1585919 126
FAUSER TABLE T_YGZ_GZZZ_MX 7 1585922 1585983 62
FAUSER TABLE T_YGZ_GZZZ_MX 7 1586000 1586047 48
FAUSER TABLE T_YGZ_GZZZ_MX 7 1586050 1586175 126
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ------------------ -------------------- ------------------------------ ---------- ----------------- --------------- ---------------- --------------
FAUSER INDEX PK_A2018026ZQXX 7 1586306 1586335 30
FAUSER INDEX PK_A2018026ZQXX 7 1586352 1586383 32
FAUSER INDEX PK_A2018026ZQXX 7 1586400 1586431 32
BXGZ TABLE TMPBALBAL 8 2487554 2487571 18
BXGZ TABLE A2018008DKFWTZ 8 2487682 2487687 6
BXGZ TABLE A2018008DKFWTZ 8 2487689 2487695 7
BXGZ TABLE A2018008DKFWTZ 8 2487728 2487735 8
BXGZ TABLE A2018008DKFWTZ 8 2487737 2487751 15
BXGZ TABLE A2018008DKFWTZ 8 2487753 2487759 7
8 2492969 2492983 15 Free Block
8 2492985 2492999 15 Free Block
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ------------------ -------------------- ------------------------------ ---------- ----------------- --------------- ---------------- --------------
8 2493001 2493015 15 Free Block
8 2493017 2493031 15 Free Block
8 2493033 2493047 15 Free Block
8 2493049 2493055 7 Free Block
BXGZ TABLE CSJJXX 8 2493444 2493447 4
BXGZ TABLE CSJJXX 8 2493451 2493463 13
BXGZ TABLE CSJJXX 8 2493465 2493479 15
BXGZ TABLE CSJJXX 8 2493481 2493495 15
BXGZ TABLE CSJJXX 8 2493497 2493511 15
BXGZ TABLE CSJJXX 8 2493513 2493527 15
BXGZ TABLE CSJJXX 8 2493529 2493543 15
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ------------------ -------------------- ------------------------------ ---------- ----------------- --------------- ---------------- --------------
BXGZ TABLE CSJJXX 8 2493545 2493559 15
BXGZ TABLE CSJJXX 8 2493561 2493567 7
BXGZ TABLE CSJJXX 8 2493570 2493695 126
BXGZ TABLE CSJJXX 8 2493698 2493823 126
BXGZ TABLE CSJJXX 8 2493826 2493951 126
BXGZ TABLE CSJJXX 8 2493954 2494079 126
BXGZ TABLE CSJJXX 8 2494082 2494207 126
BXGZ INDEX SYS_C00598600 8 2494466 2494543 78
BXGZ INDEX SYS_C00598600 8 2494576 2494591 16
BXGZ TABLE A005JJHZGZB 8 2494672 2494719 48
BXGZ INDEX SYS_C00591149 8 2494724 2494750 27
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ------------------ -------------------- ------------------------------ ---------- ----------------- --------------- ---------------- --------------
BXGZ INDEX SYS_C00591149 8 2494848 2494855 8
BXGZ INDEX SYS_C00591149 8 2494978 2495103 126
BXGZ INDEX SYS_C00591149 8 2495106 2495231 126
BXGZ INDEX SYS_C00591149 8 2495234 2495248 15
*********************
尝试验证全库
*********************
VALIDATE DATABASE;
report unrecoverable;
select coount(*) from v$database_block_coruption
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@FA-bak ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 23 16:46:23 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: FADB (DBID=2014267913)
RMAN> VALIDATE DATABASE;
Starting validate at 23-JUL-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=196 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/fapdb/fabak/system.260.977591191
input datafile file number=00005 name=/fapdb/fabak/users.265.977591219
input datafile file number=00006 name=/fapdb/fabak/fauser.270.977742491
input datafile file number=00007 name=/fapdb/fabak/fauser.271.977742525
input datafile file number=00008 name=/fapdb/fabak/bxgz.272.977742545
input datafile file number=00003 name=/fapdb/fabak/undotbs1.262.977591197
input datafile file number=00004 name=/fapdb/fabak/undotbs2.264.977591217
input datafile file number=00002 name=/fapdb/fabak/sysaux.261.977591195
input datafile file number=00010 name=/fapdb/fabak/xbrl.274.977742689
input datafile file number=00011 name=/fapdb/fabak/qdii.278.979298287
input datafile file number=00009 name=/fapdb/fabak/ogg.273.977742577
channel ORA_DISK_1: validation complete, elapsed time: 00:13:46
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 12779 4096015 247888902478
File Name: /fapdb/fabak/system.260.977591191
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 214859
Index 0 37807
Other 0 3830555
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 11592 1310767 247888888322
File Name: /fapdb/fabak/sysaux.261.977591195
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 102499
Index 0 119033
Other 0 1077596
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 1 3932168 247888936013
File Name: /fapdb/fabak/undotbs1.262.977591197
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 3932159
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 1 3932168 247888902295
File Name: /fapdb/fabak/undotbs2.264.977591217
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 3932159
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 2382 4096022 247888903160
File Name: /fapdb/fabak/users.265.977591219
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 305392
Index 0 73000
Other 0 3715226
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 1644 38036 4096042 247889001216
File Name: /fapdb/fabak/fauser.270.977742491
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1025642
Index 0 432334
Other 0 2599988
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 1461 40052 4096028 247889001296
File Name: /fapdb/fabak/fauser.271.977742525
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1023250
Index 0 454589
Other 0 2578109
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8 OK 1331 22936 4096224 247889046970
File Name: /fapdb/fabak/bxgz.272.977742545
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1899015
Index 0 540778
Other 0 1633271
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 411 256001 5035203
File Name: /fapdb/fabak/ogg.273.977742577
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6
Index 0 45
Other 0 255538
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10 OK 0 1159 1310728 247888976018
File Name: /fapdb/fabak/xbrl.274.977742689
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 42711
Index 0 3399
Other 0 1263451
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
11 OK 0 4901 1310722 246722332762
File Name: /fapdb/fabak/qdii.278.979298287
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 995
Index 0 644
Other 0 1304180
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 1154
Finished validate at 23-JUL-18
81 rows selected.
*********************
check datafile 6、7、8
*********************
backup check logical validate datafile 8;
run{
allocate channel c0 type disk;
allocate channel c1 type disk;
backup datafile 6 format '/faarch/bak/fauser.270.977742491';
backup datafile 7 format '/faarch/bak/fauser.271.977742525 ';
backup datafile 8 format '/faarch/bak/bxgz.272.977742545';
sql 'alter system archive log current';
backup archivelog all format '/rman/prod_arch_%U';
release channel c0;
release channel c1;
}
ILE_NAME FILE_ID
-------------------------------------------------- ----------
+DATADG/fadb/datafile/fauser.270.977742491 6
+DATADG/fadb/datafile/fauser.271.977742525 7
+DATADG/fadb/datafile/bxgz.272.977742545 8
*********************
check之后还是有很多坏块
*********************
*********************
最终采用备份数据文件的方式做恢复
*********************
run{
allocate channel c0 type disk;
allocate channel c1 type disk;
backup datafile 6 format '/faarch/bak/fauser.270.977742491';
backup datafile 7 format '/faarch/bak/fauser.271.977742525';
backup datafile 8 format '/faarch/bak/fauser.272.977742545';
sql 'alter system archive log current';
backup archivelog all format '/rman/prod_arch_%U';
release channel c0;
release channel c1;
}
*********************
在备库关闭mrp
*********************
alter database recover managed standby database cancel;
[oracle@FA-bak ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 23 17:42:33 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CONNECTED
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
10 rows selected.
*********************
copy备份文件至备库
*********************
scp bxgz.272.977742545 fauser.270.977742491 fauser.271.977742525 10.4.175.13:/faarch/rman
*********************
在备库做数据恢复
*********************
catalog start with ’/fapdb/fabak/‘
run{
allocate channel c0 type disk;
allocate channel c1 type disk;
set newname for datafile 6 to '/fapdb/fabak/fauser.270.977742491';
set newname for datafile 7 to '/fapdb/fabak/fauser.271.977742525';
set newname for datafile 8 to '/fapdb/fabak/bxgz.272.977742545';
restore datafile 6;
restore datafile 7;
restore datafile 8;
switch datafile all;
switch tempfile all;
release channel c0;
release channel c1;
}
RMAN> run{
2> allocate channel c0 type disk;
3> allocate channel c1 type disk;
4> set newname for datafile 6 to '/fapdb/fabak/fauser.270.977742491';
5> set newname for datafile 7 to '/fapdb/fabak/fauser.271.977742525';
6> set newname for datafile 8 to '/fapdb/fabak/bxgz.272.977742545';
7> restore datafile 6;
8> restore datafile 7;
9> restore datafile 8;
10> switch datafile all;
11> switch tempfile all;
12> release channel c0;
13> release channel c1;
14> }
allocated channel: c0
channel c0: SID=194 device type=DISK
allocated channel: c1
channel c1: SID=242 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 23-JUL-18
channel c0: starting datafile backup set restore
channel c0: specifying datafile(s) to restore from backup set
channel c0: restoring datafile 00006 to /fapdb/fabak/fauser.270.977742491
channel c0: reading from backup piece /faarch/bak/db_FADB_20180713_04t7suea_1_1
channel c0: ORA-19870: error while restoring backup piece /faarch/bak/db_FADB_20180713_04t7suea_1_1
ORA-19505: failed to identify file "/faarch/bak/db_FADB_20180713_04t7suea_1_1"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
failover to previous backup
creating datafile file number=6 name=/fapdb/fabak/fauser.270.977742491
Finished restore at 23-JUL-18
Starting restore at 23-JUL-18
channel c0: starting datafile backup set restore
channel c0: specifying datafile(s) to restore from backup set
channel c0: restoring datafile 00007 to /fapdb/fabak/fauser.271.977742525
channel c0: reading from backup piece /faarch/bak/db_FADB_20180713_05t7sueb_1_1
channel c0: ORA-19870: error while restoring backup piece /faarch/bak/db_FADB_20180713_05t7sueb_1_1
ORA-19505: failed to identify file "/faarch/bak/db_FADB_20180713_05t7sueb_1_1"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
failover to previous backup
creating datafile file number=7 name=/fapdb/fabak/fauser.271.977742525
Finished restore at 23-JUL-18
Starting restore at 23-JUL-18
channel c0: starting datafile backup set restore
channel c0: specifying datafile(s) to restore from backup set
channel c0: restoring datafile 00008 to /fapdb/fabak/bxgz.272.977742545
channel c0: reading from backup piece /faarch/bak/db_FADB_20180713_02t7suea_1_1
channel c0: ORA-19870: error while restoring backup piece /faarch/bak/db_FADB_20180713_02t7suea_1_1
ORA-19505: failed to identify file "/faarch/bak/db_FADB_20180713_02t7suea_1_1"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
failover to previous backup
creating datafile file number=8 name=/fapdb/fabak/bxgz.272.977742545
Finished restore at 23-JUL-18
released channel: c0
released channel: c1
*********************
开启mrp
*********************
alter database recover managed standby database disconnect from session；
浙公网安备 33010602011771号