Objective: Demonstrate the automatic block media recovery (ABMR) in oracle (ORA-1578)
Version: 12.2.0.1
primary db: GGSRC04T
standby db: GGSRC04TSB1
CDB: NO (but ideally it makes no difference)
Prereqs: the standby database needs to be in active dataguard mode (a real-time query physical standby database)
Caution: For block media recovery to work automatically, the physical standby database must be in real-time query mode. An Oracle Active Data Guard license is required.
Step 1) Verify the DG configuration
set lines 1200 pages 3000 colsep , time on timing on trim on trims on
alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
select name,open_mode from v$database;
select instance_name,status,startup_time from v$instance;
show parameter config;
show parameter log_archive_dest;
select process,status,client_process,sequence# from gv$managed_standby;
select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;
select * from v$log;
select * from v$standby_log;
alter database recover automatic managed standby database disconnect;
select process,status,client_process,sequence# from gv$managed_standby;
Output: Primary:
SQL> set lines 1200 pages 3000 colsep , time on timing on trim on trims on alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';11:43:28 SQL> Session altered. Elapsed: 00:00:00.00 11:43:29 SQL> select name,open_mode from v$database; NAME ,OPEN_MODE ---------,-------------------- GGSRC04T ,READ WRITE Elapsed: 00:00:00.00 11:43:33 SQL> select instance_name,status,startup_time from v$instance; INSTANCE_NAME ,STATUS ,STARTUP_TIME ----------------,------------,-------------------- GGSRC04T ,OPEN ,26/MAY/2024 10:23:09 Elapsed: 00:00:00.00 11:43:37 SQL> show parameter config; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ dg_broker_config_file1 ,string ,/u01/app/oracle/product/12.2.0 , ,.1/db_1/dbs/dr1GGSRC04T.dat dg_broker_config_file2 ,string ,/u01/app/oracle/product/12.2.0 , ,.1/db_1/dbs/dr2GGSRC04T.dat log_archive_config ,string ,DG_CONFIG=(GGSRC04T,GGSRC04TSB , ,1) 11:43:43 SQL> show parameter log_archive_dest; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ log_archive_dest ,string , log_archive_dest_1 ,string ,LOCATION=USE_DB_RECOVERY_FILE_ , ,DEST VALID_FOR=(ALL_LOGFILES,A , ,LL_ROLES) DB_UNIQUE_NAME=GGSRC , ,04T log_archive_dest_10 ,string , log_archive_dest_11 ,string , log_archive_dest_12 ,string , log_archive_dest_13 ,string , log_archive_dest_14 ,string , log_archive_dest_15 ,string , log_archive_dest_16 ,string , log_archive_dest_17 ,string , log_archive_dest_18 ,string , log_archive_dest_19 ,string , log_archive_dest_2 ,string ,SERVICE=GGSRC04TSB1 ASYNC VALI , ,D_FOR=(ONLINE_LOGFILES,PRIMARY , ,_ROLE) DB_UNIQUE_NAME=GGSRC04T , ,SB1 log_archive_dest_20 ,string , log_archive_dest_21 ,string , log_archive_dest_22 ,string , log_archive_dest_23 ,string , log_archive_dest_24 ,string , log_archive_dest_25 ,string , log_archive_dest_26 ,string , log_archive_dest_27 ,string , log_archive_dest_28 ,string , log_archive_dest_29 ,string , log_archive_dest_3 ,string , log_archive_dest_30 ,string , log_archive_dest_31 ,string , log_archive_dest_4 ,string , log_archive_dest_5 ,string , log_archive_dest_6 ,string , log_archive_dest_7 ,string , log_archive_dest_8 ,string , log_archive_dest_9 ,string , log_archive_dest_state_1 ,string ,enable log_archive_dest_state_10 ,string ,enable log_archive_dest_state_11 ,string ,enable log_archive_dest_state_12 ,string ,enable log_archive_dest_state_13 ,string ,enable log_archive_dest_state_14 ,string ,enable log_archive_dest_state_15 ,string ,enable log_archive_dest_state_16 ,string ,enable log_archive_dest_state_17 ,string ,enable log_archive_dest_state_18 ,string ,enable log_archive_dest_state_19 ,string ,enable log_archive_dest_state_2 ,string ,ENABLE log_archive_dest_state_20 ,string ,enable log_archive_dest_state_21 ,string ,enable log_archive_dest_state_22 ,string ,enable log_archive_dest_state_23 ,string ,enable log_archive_dest_state_24 ,string ,enable log_archive_dest_state_25 ,string ,enable log_archive_dest_state_26 ,string ,enable log_archive_dest_state_27 ,string ,enable log_archive_dest_state_28 ,string ,enable log_archive_dest_state_29 ,string ,enable log_archive_dest_state_3 ,string ,enable log_archive_dest_state_30 ,string ,enable log_archive_dest_state_31 ,string ,enable log_archive_dest_state_4 ,string ,enable log_archive_dest_state_5 ,string ,enable log_archive_dest_state_6 ,string ,enable log_archive_dest_state_7 ,string ,enable log_archive_dest_state_8 ,string ,enable log_archive_dest_state_9 ,string ,enable 11:44:00 SQL> select * from v$log; GROUP#, THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, MEMBERS,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME , CON_ID ----------,----------,----------,----------,----------,----------,---,----------------,-------------,--------------------,------------,--------------------,---------- 1, 1, 52, 209715200, 512, 1,YES,INACTIVE , 1189308,07/FEB/2024 12:24:07, 1256706,26/MAY/2024 10:23:15, 0 2, 1, 53, 209715200, 512, 1,YES,INACTIVE , 1256706,26/MAY/2024 10:23:15, 1267533,26/MAY/2024 11:23:40, 0 3, 1, 54, 209715200, 512, 1,NO ,CURRENT , 1267533,26/MAY/2024 11:23:40, 1.8447E+19, , 0 Elapsed: 00:00:00.00 11:46:15 SQL> select * from v$standby_log; GROUP#,DBID , THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, USED,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME ,LAST_CHANGE#,LAST_TIME , CON_ID ----------,----------------------------------------,----------,----------,----------,----------,----------,---,----------,-------------,--------------------,------------,--------------------,------------,--------------------,---------- 4,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 5,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 6,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 7,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 Elapsed: 00:00:00.00 11:46:21 SQL> |
Output: Standby:
SQL> set lines 1200 pages 3000 colsep , time on timing on trim on trims on alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';11:44:33 SQL> Session altered. Elapsed: 00:00:00.00 11:44:34 SQL> select name,open_mode from v$database; NAME ,OPEN_MODE ---------,-------------------- GGSRC04T ,MOUNTED Elapsed: 00:00:00.02 11:44:38 SQL> select instance_name,status,startup_time from v$instance; INSTANCE_NAME ,STATUS ,STARTUP_TIME ----------------,------------,-------------------- GGSRC04TSB1 ,MOUNTED ,26/MAY/2024 11:23:30 Elapsed: 00:00:00.01 11:44:42 SQL> show parameter config; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ dg_broker_config_file1 ,string ,/u01/app/oracle/product/12.2.0 , ,.1/db_1/dbs/dr1GGSRC04TSB1.dat dg_broker_config_file2 ,string ,/u01/app/oracle/product/12.2.0 , ,.1/db_1/dbs/dr2GGSRC04TSB1.dat log_archive_config ,string ,DG_CONFIG=(GGSRC04T,GGSRC04TSB , ,1) 11:44:49 SQL> show parameter log_archive_dest; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ log_archive_dest ,string , log_archive_dest_1 ,string ,LOCATION=USE_DB_RECOVERY_FILE_ , ,DEST VALID_FOR=(ALL_LOGFILES,A , ,LL_ROLES) DB_UNIQUE_NAME=GGSR , ,C04TSB1 log_archive_dest_10 ,string , log_archive_dest_11 ,string , log_archive_dest_12 ,string , log_archive_dest_13 ,string , log_archive_dest_14 ,string , log_archive_dest_15 ,string , log_archive_dest_16 ,string , log_archive_dest_17 ,string , log_archive_dest_18 ,string , log_archive_dest_19 ,string , log_archive_dest_2 ,string ,service=GGSRC04T ASYNC valid_f , ,or=(ONLINE_LOGFILE,PRIMARY_ROL , ,E) db_unique_name=GGSRC04T log_archive_dest_20 ,string , log_archive_dest_21 ,string , log_archive_dest_22 ,string , log_archive_dest_23 ,string , log_archive_dest_24 ,string , log_archive_dest_25 ,string , log_archive_dest_26 ,string , log_archive_dest_27 ,string , log_archive_dest_28 ,string , log_archive_dest_29 ,string , log_archive_dest_3 ,string , log_archive_dest_30 ,string , log_archive_dest_31 ,string , log_archive_dest_4 ,string , log_archive_dest_5 ,string , log_archive_dest_6 ,string , log_archive_dest_7 ,string , log_archive_dest_8 ,string , log_archive_dest_9 ,string , log_archive_dest_state_1 ,string ,enable log_archive_dest_state_10 ,string ,enable log_archive_dest_state_11 ,string ,enable log_archive_dest_state_12 ,string ,enable log_archive_dest_state_13 ,string ,enable log_archive_dest_state_14 ,string ,enable log_archive_dest_state_15 ,string ,enable log_archive_dest_state_16 ,string ,enable log_archive_dest_state_17 ,string ,enable log_archive_dest_state_18 ,string ,enable log_archive_dest_state_19 ,string ,enable log_archive_dest_state_2 ,string ,enable log_archive_dest_state_20 ,string ,enable log_archive_dest_state_21 ,string ,enable log_archive_dest_state_22 ,string ,enable log_archive_dest_state_23 ,string ,enable log_archive_dest_state_24 ,string ,enable log_archive_dest_state_25 ,string ,enable log_archive_dest_state_26 ,string ,enable log_archive_dest_state_27 ,string ,enable log_archive_dest_state_28 ,string ,enable log_archive_dest_state_29 ,string ,enable log_archive_dest_state_3 ,string ,enable log_archive_dest_state_30 ,string ,enable log_archive_dest_state_31 ,string ,enable log_archive_dest_state_4 ,string ,enable log_archive_dest_state_5 ,string ,enable log_archive_dest_state_6 ,string ,enable log_archive_dest_state_7 ,string ,enable log_archive_dest_state_8 ,string ,enable log_archive_dest_state_9 ,string ,enable 11:44:57 SQL> select process,status,client_process,sequence# from gv$managed_standby; PROCESS ,STATUS ,CLIENT_P, SEQUENCE# ---------,------------,--------,---------- ARCH ,CONNECTED ,ARCH , 0 DGRD ,ALLOCATED ,N/A , 0 DGRD ,ALLOCATED ,N/A , 0 ARCH ,CONNECTED ,ARCH , 0 ARCH ,CLOSING ,ARCH , 53 ARCH ,CONNECTED ,ARCH , 0 RFS ,IDLE ,Archival, 0 RFS ,IDLE ,LGWR , 54 8 rows selected. Elapsed: 00:00:00.01 11:45:15 SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT; no rows selected Elapsed: 00:00:00.01 11:45:21 SQL> alter database recover automatic managed standby database; ^Calter database recover automatic managed standby database * ERROR at line 1: ORA-16043: Redo apply has been canceled. ORA-01013: user requested cancel of current operation Elapsed: 00:00:02.08 11:45:28 SQL> alter database recover automatic managed standby database disconnect; Database altered. Elapsed: 00:00:06.03 11:45:41 SQL> select process,status,client_process,sequence# from gv$managed_standby; PROCESS ,STATUS ,CLIENT_P, SEQUENCE# ---------,------------,--------,---------- ARCH ,CONNECTED ,ARCH , 0 DGRD ,ALLOCATED ,N/A , 0 DGRD ,ALLOCATED ,N/A , 0 ARCH ,CONNECTED ,ARCH , 0 ARCH ,CLOSING ,ARCH , 53 ARCH ,CONNECTED ,ARCH , 0 RFS ,IDLE ,Archival, 0 RFS ,IDLE ,LGWR , 54 MRP0 ,APPLYING_LOG,N/A , 54 9 rows selected. Elapsed: 00:00:00.00 11:45:52 SQL> select * from v$log; GROUP#, THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, MEMBERS,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME , CON_ID ----------,----------,----------,----------,----------,----------,---,----------------,-------------,--------------------,------------,--------------------,---------- 1, 1, 0, 209715200, 512, 1,YES,UNUSED , 600558,04/SEP/2023 11:05:52, 1.8447E+19, , 0 3, 1, 0, 209715200, 512, 1,YES,UNUSED , 0, , 0, , 0 2, 1, 0, 209715200, 512, 1,YES,UNUSED , 0, , 0, , 0 Elapsed: 00:00:00.01 11:46:00 SQL> select * from v$standby_log; GROUP#,DBID , THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, USED,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME ,LAST_CHANGE#,LAST_TIME , CON_ID ----------,----------------------------------------,----------,----------,----------,----------,----------,---,----------,-------------,--------------------,------------,--------------------,------------,--------------------,---------- 4,UNASSIGNED , 1, 0, 209715200, 512, 0,NO ,UNASSIGNED, , , , , , , 0 5,4198404018 , 1, 54, 209715200, 512, 1125376,YES,ACTIVE , 1267533,26/MAY/2024 11:23:40, , , 1270664,26/MAY/2024 11:46:05, 0 6,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 7,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 Elapsed: 00:00:00.01 |
2) Setup test user & tablespace
create tablespace TbSPC_ABMR_26MAY datafile size 100m autoextend on next 100m;
create tablespace TbSPC_ABMR_INDX_26MAY datafile size 100m autoextend on next 100m;
create user user_abmr identified by "user_abmr" default tablespace TbSPC_ABMR_26MAY;
grant connect, create session, resource to user_abmr;
alter user user_abmr quota unlimited on TbSPC_ABMR_26MAY;
alter user user_abmr quota unlimited on TbSPC_ABMR_INDX_26MAY;
grant create table to user_abmr;
grant alter session to user_abmr;
Output: Primary:
11:46:21 SQL> create tablespace TbSPC_ABMR_26MAY datafile size 100m autoextend on next 100m; Tablespace created. Elapsed: 00:00:00.60 11:47:32 SQL> create tablespace TbSPC_ABMR_INDX_26MAY datafile size 100m autoextend on next 100m; Tablespace created. Elapsed: 00:00:01.02 11:47:39 SQL> create user user_abmr identified by "user_abmr" default tablespace TbSPC_ABMR_26MAY; User created. Elapsed: 00:00:00.10 11:48:54 SQL> grant connect, create session, resource to user_abmr; Grant succeeded. Elapsed: 00:00:00.02 11:48:59 SQL> alter user user_abmr quota unlimited on TbSPC_ABMR_26MAY; User altered. Elapsed: 00:00:00.02 11:49:03 SQL> alter user user_abmr quota unlimited on TbSPC_ABMR_INDX_26MAY; User altered. Elapsed: 00:00:00.01 11:49:07 SQL> grant create table to user_abmr; Grant succeeded. Elapsed: 00:00:00.01 11:49:11 SQL> grant alter session to user_abmr; Grant succeeded. Elapsed: 00:00:00.00 11:49:15 SQL> |
3) Create a test table and index
create table tbl_abmr_26may (id number, name varchar2(100)) tablespace TbSPC_ABMR_26MAY;
create index idx_abmr_26may on tbl_abmr_26may (id) tablespace TbSPC_ABMR_INDX_26MAY;
Output:
SQL> create table tbl_abmr_26may (id number, name varchar2(100)) tablespace TbSPC_ABMR_26MAY; Table created. SQL> create index idx_abmr_26may on tbl_abmr_26may (id) tablespace TbSPC_ABMR_INDX_26MAY; Index created. SQL> show user; USER is "USER_ABMR" SQL> |
4) Load test data
alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
insert into tbl_abmr_26may
select level id
,dbms_random.string('U',5) name
from dual
connect by level < 1000;
commit;
>>>>>>>>>>>>>>>>>Round 1: Standby in non active dg mode
Flush buffer cache:
alter system flush buffer_cache;
5) Verify the data accessibilty using both Full Table Scan and index scan
set lines 1200 pages 3000 colsep , time on timing on trim on trims on
alter session set tracefile_identifier='ABMR';
alter session set statistics_level=ALL;
alter session set max_dump_file_size=UNLIMITED;
alter session set events '10046 trace name context forever, level 12';
explain plan for
select /*+ full(t1) */ * from tbl_abmr_26may t1 where id=100;
set autotrace on
select /*+ full(t1) */ * from tbl_abmr_26may t1 where id=100;
set autotrace off
explain plan for
select * from tbl_abmr_26may t1 where id=100;
set autotrace on
select * from tbl_abmr_26may t1 where id=100;
set autotrace off
--verify the alert log to see if any errors.
Output:
SQL> set lines 1200 pages 3000 colsep , time on timing on trim on trims on alter session set tracefile_identifier='ABMR'; alter session set statistics_level=ALL; alter session set max_dump_file_size=UNLIMITED; alter session set events '10046 trace name context forever, level 12';11:55:21 SQL> Session altered. Elapsed: 00:00:00.00 11:55:21 SQL> Session altered. Elapsed: 00:00:00.00 11:55:21 SQL> Session altered. Elapsed: 00:00:00.00 11:55:21 SQL> Session altered. Elapsed: 00:00:00.00 11:55:22 SQL> explain plan for select /*+ full(t1) */ * from tbl_abmr_26may t1 where id=100;11:55:34 2 Explained. Elapsed: 00:00:00.01 11:55:35 SQL> @?/rdbms/admin/utlxplp.sql PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 141468548 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 65 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TBL_ABMR_26MAY | 1 | 65 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=100) Note ----- - dynamic statistics used: dynamic sampling (level=2) 17 rows selected. Elapsed: 00:00:00.04 11:55:43 SQL> set autotrace on 11:55:50 SQL> select /*+ full(t1) */ * from tbl_abmr_26may t1 where id=100; ID,NAME ----------,---------------------------------------------------------------------------------------------------- 100,MBJXM Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 141468548 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 65 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TBL_ABMR_26MAY | 1 | 65 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=100) Note ----- - dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 608 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 11:55:54 SQL> set autotrace off 11:56:08 SQL> explain plan for select * from tbl_abmr_26may t1 where id=100;11:56:23 2 Explained. Elapsed: 00:00:00.02 11:56:24 SQL> @?/rdbms/admin/utlxplp.sql PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1876658026 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 65 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL_ABMR_26MAY | 1 | 65 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_ABMR_26MAY | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=100) Note ----- - dynamic statistics used: dynamic sampling (level=2) 18 rows selected. Elapsed: 00:00:00.03 11:56:28 SQL> >>> index usage is skipped. |
6) Corrupt the block
select table_name,tablespace_name from dba_tables where owner='USER_ABMR' and table_name='TBL_ABMR_26MAY';
select file_id,file_name from dba_data_files where tablespace_name='TBSPC_ABMR_26MAY';
select * from (select distinct dbms_rowid.rowid_block_number(rowid) from USER_ABMR.TBL_ABMR_26MAY where id=100);
alter system dump datafile 8 block 131;
select tracefile from v$process where addr=(select paddr from v$session where sid=(select distincT(sid) from v$mystat));
before:
/u01/app/oracle/diag/rdbms/ggsrc04t/GGSRC04T/trace/GGSRC04T_ora_10034.trc
-- block number found from above
dd of=/oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf bs=8192 seek=131 conv=notrunc count=1 if=/dev/zero
set lines 1200 pages 3000 colsep , time on timing on trim on trims on
select * from v$database_block_corruption;
select * from v$nonlogged_block;
validate datafile 8;
select * from v$database_block_corruption;
select * from v$nonlogged_block;
Output:
11:54:40 SQL> select table_name,tablespace_name from dba_tables where owner='USER_ABMR' and table_name='TBL_ABMR_26MAY'; TABLE_NAME ,TABLESPACE_NAME --------------------------------------------------------------------------------------------------------------------------------,------------------------------ TBL_ABMR_26MAY ,TBSPC_ABMR_26MAY Elapsed: 00:00:00.23 11:57:35 SQL> select file_id,file_name from dba_data_files where tablespace_name='TBSPC_ABMR_26MAY'; FILE_ID,FILE_NAME ----------,--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 8,/oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf Elapsed: 00:00:00.07 11:57:45 SQL> select * from (select distinct dbms_rowid.rowid_block_number(rowid) from USER_ABMR.TBL_ABMR_26MAY where id=100); DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ------------------------------------ 131 Elapsed: 00:00:00.07 11:57:54 SQL> sho parameter block NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ db_block_buffers ,integer ,0 db_block_checking ,string ,FALSE db_block_checksum ,string ,TYPICAL db_block_size ,integer ,8192 db_file_multiblock_read_count ,integer ,128 11:58:08 SQL> alter system dump datafile 8 block 131; System altered. Elapsed: 00:00:00.02 12:00:11 SQL> select tracefile from v$process where addr=(select paddr from v$session where sid=(select distincT(sid) from v$mystat))); select tracefile from v$process where addr=(select paddr from v$session where sid=(select distincT(sid) from v$mystat))) * ERROR at line 1: ORA-00933: SQL command not properly ended Elapsed: 00:00:00.00 12:00:40 SQL> select tracefile from v$process where addr=(select paddr from v$session where sid=(select distincT(sid) from v$mystat)); TRACEFILE --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/ggsrc04t/GGSRC04T/trace/GGSRC04T_ora_10034.trc Elapsed: 00:00:00.02 12:00:47 SQL> [oracle@vcentos79-oracle-ggsrc ~]$ view /u01/app/oracle/diag/rdbms/ggsrc04t/GGSRC04T/trace/GGSRC04T_ora_10034.trc [oracle@vcentos79-oracle-ggsrc ~]$ ls -altr /u01/app/oracle/diag/rdbms/ggsrc04t/GGSRC04T/trace/GGSRC04T_ora_10034.trc -rw-r-----. 1 oracle oinstall 102061 May 26 12:00 /u01/app/oracle/diag/rdbms/ggsrc04t/GGSRC04T/trace/GGSRC04T_ora_10034.trc [oracle@vcentos79-oracle-ggsrc ~]$ dd of=/oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf bs=8192 seek=131 conv=notrunc count=1 if=/dev/zero 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 0.000163821 s, 50.0 MB/s [oracle@vcentos79-oracle-ggsrc ~]$ 12:00:47 SQL> select * from v$database_block_corruption; no rows selected Elapsed: 00:00:00.00 12:03:50 SQL> select * from v$nonlogged_block; no rows selected Elapsed: 00:00:00.01 12:03:56 SQL> ! [oracle@vcentos79-oracle-ggsrc ABMR_ORA1578]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Sun May 26 12:04:02 2024 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: GGSRC04T (DBID=4198404018) RMAN> validate datafile 8; Starting validate at 26-MAY-24 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=65 device type=DISK channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00008 name=/oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:01 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 8 FAILED 0 12665 12800 1272499 File Name: /oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 4 Index 0 0 Other 1 131 validate found one or more corrupt blocks See trace file /u01/app/oracle/diag/rdbms/ggsrc04t/GGSRC04T/trace/GGSRC04T_ora_11446.trc for details Finished validate at 26-MAY-24 RMAN> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID ---------- ---------- ---------- ------------------ --------- ---------- 8 131 1 0 ALL ZERO 0 RMAN> select * from v$nonlogged_block; no rows selected RMAN> exit Recovery Manager complete. [oracle@vcentos79-oracle-ggsrc ABMR_ORA1578]$ exit exit 12:04:40 SQL> SQL> alter system dump datafile 8 block 131; System altered. SQL> select tracefile from v$process where addr=(select paddr from v$session where sid=(select distincT(sid) from v$mystat)); TRACEFILE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/ggsrc04t/GGSRC04T/trace/GGSRC04T_ora_11509.trc [oracle@vcentos79-oracle-ggsrc ABMR_ORA1578]$ cp /u01/app/oracle/diag/rdbms/ggsrc04t/GGSRC04T/trace/GGSRC04T_ora_11509.trc BAD_GGSRC04T_ora_11509.trc [oracle@vcentos79-oracle-ggsrc ABMR_ORA1578]$ cp /u01/app/oracle/diag/rdbms/ggsrc04t/GGSRC04T/trace/GGSRC04T_ora_10034.trc GOOD_GGSRC04T_ora_10034.trc [oracle@vcentos79-oracle-ggsrc ABMR_ORA1578]$ view BAD_GGSRC04T_ora_11509.trc |
7) Flush the buffer cache
alter system flush buffer_cache;
8) Retry selecting the data
alter session set tracefile_identifier='ABMR';
alter session set statistics_level=ALL;
alter session set max_dump_file_size=UNLIMITED;
alter session set events '10046 trace name context forever, level 12';
explain plan for
select /*+ full(t1) */ * from tbl_abmr_26may t1 where id=100;
set autotrace on
select /*+ full(t1) */ * from tbl_abmr_26may t1 where id=100;
set autotrace off
explain plan for
select * from tbl_abmr_26may t1 where id=100;
set autotrace on
select * from tbl_abmr_26may t1 where id=100;
set autotrace off
--verify the alert log to see if any errors.
Output:
SQL> set lines 1200 pages 3000 SQL> @?/rdbms/admin/utlxplp.sql PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 141468548 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 65 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TBL_ABMR_26MAY | 1 | 65 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=100) Note ----- - dynamic statistics used: dynamic sampling (level=2) 17 rows selected. SQL> set lines 1200 pages 3000 colsep , time on timing on trim on trims on 12:08:06 SQL> @?/rdbms/admin/utlxplp.sql PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 141468548 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 65 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TBL_ABMR_26MAY | 1 | 65 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=100) Note ----- - dynamic statistics used: dynamic sampling (level=2) 17 rows selected. Elapsed: 00:00:00.02 12:08:09 SQL> set autotrace on 12:08:22 SQL> select /*+ full(t1) */ * from tbl_abmr_26may t1 where id=100; select /*+ full(t1) */ * from tbl_abmr_26may t1 where id=100 * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 8, block # 131) ORA-01110: data file 8: '/oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf' Elapsed: 00:00:02.34 12:08:29 SQL> set autotrace off 12:09:36 SQL> Alertlog: 2024-05-26 12:08:28.451000 +01:00 ***************************************************************** An internal routine has requested a dump of selected redo. This usually happens following a specific internal error, when analysis of the redo logs will help Oracle Support with the diagnosis. It is recommended that you retain all the redo logs generated (by all the instances) during the past 12 hours, in case additional redo dumps are required to help with the diagnosis. ***************************************************************** 2024-05-26 12:08:29.570000 +01:00 Corrupt block relative dba: 0x02000083 (file 8, block 131) Completely zero block found during validation Reread of blocknum=131, file=/oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf. found same corrupt data Reread of blocknum=131, file=/oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf. found same corrupt data Reread of blocknum=131, file=/oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf. found same corrupt data Reread of blocknum=131, file=/oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf. found same corrupt data Reread of blocknum=131, file=/oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf. found same corrupt data 2024-05-26 12:10:00.489000 +01:00 |
9) Fix the corruption manually, since ABMR didnt fire
set lines 1200 pages 3000 colspe , time on timing on trim on trims on
select * from v$database_block_corruption;
select * from v$nonlogged_block;
validate datafile <fileid>;
select * from v$database_block_corruption;
select * from v$nonlogged_block;
recover corruption list;
set lines 1200 pages 3000 colspe , time on timing on trim on trims on
select * from v$database_block_corruption;
select * from v$nonlogged_block;
validate datafile <fileid>;
select * from v$database_block_corruption;
select * from v$nonlogged_block;
-- check alert log
Routine 1 -- FAILED!!!!!!!!!
RMAN> select * from v$database_block_corruption; using target database control file instead of recovery catalog FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID ---------- ---------- ---------- ------------------ --------- ---------- 8 131 1 0 ALL ZERO 0 RMAN> recover corruption list; Starting recover at 26-MAY-24 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=61 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 05/26/2024 12:12:18 RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 8 found to restore RMAN> recover datafile 8 block 131; Starting recover at 26-MAY-24 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 05/26/2024 12:13:45 RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 8 found to restore RMAN> |
8) Retry selecting the data ---- FAILED!!!! because of missing good backup of the datafile
alter session set tracefile_identifier='ABMR';
alter session set statistics_level=ALL;
alter session set max_dump_file_size=UNLIMITED;
alter session set events '10046 trace name context forever, level 12';
explain plan for
select /*+ full(t1) */ * from tbl_abmr_26may t1 where id=100;
set autotrace on
select /*+ full(t1) */ * from tbl_abmr_26may t1 where id=100;
set autotrace off
explain plan for
select * from tbl_abmr_26may t1 where id=100;
set autotrace on
select * from tbl_abmr_26may t1 where id=100;
set autotrace off
--verify the alert log to see if any errors.
>>>>>>>>>>>>>>>>>Round 2: Enable the standby databse (active mode)
Caution: Active standby or dataguard is a licensed feature of oracle; so excercise this option in your work env. carefully.
9) open the standby db in read only mode
alter database recover managed standby database cancel;
alter database open;
set lines 1200 pages 3000 colsep , time on timing on trim on trims on
alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
select name,open_mode from v$database;
select instance_name,status,startup_time from v$instance;
show parameter config;
show parameter log_archive_dest;
select process,status,client_process,sequence# from gv$managed_standby;
select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;
select * from v$log;
select * from v$standby_log;
alter database recover automatic managed standby database disconnect;
select process,status,client_process,sequence# from gv$managed_standby;
Output:
SQL> set lines 1200 pages 3000 colsep , time on timing on trim on trims on alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';11:44:33 SQL> Session altered. Elapsed: 00:00:00.00 11:44:34 SQL> select name,open_mode from v$database; NAME ,OPEN_MODE ---------,-------------------- GGSRC04T ,MOUNTED Elapsed: 00:00:00.02 11:44:38 SQL> select instance_name,status,startup_time from v$instance; INSTANCE_NAME ,STATUS ,STARTUP_TIME ----------------,------------,-------------------- GGSRC04TSB1 ,MOUNTED ,26/MAY/2024 11:23:30 Elapsed: 00:00:00.01 11:44:42 SQL> show parameter config; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ dg_broker_config_file1 ,string ,/u01/app/oracle/product/12.2.0 , ,.1/db_1/dbs/dr1GGSRC04TSB1.dat dg_broker_config_file2 ,string ,/u01/app/oracle/product/12.2.0 , ,.1/db_1/dbs/dr2GGSRC04TSB1.dat log_archive_config ,string ,DG_CONFIG=(GGSRC04T,GGSRC04TSB , ,1) 11:44:49 SQL> show parameter log_archive_dest; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ log_archive_dest ,string , log_archive_dest_1 ,string ,LOCATION=USE_DB_RECOVERY_FILE_ , ,DEST VALID_FOR=(ALL_LOGFILES,A , ,LL_ROLES) DB_UNIQUE_NAME=GGSR , ,C04TSB1 log_archive_dest_10 ,string , log_archive_dest_11 ,string , log_archive_dest_12 ,string , log_archive_dest_13 ,string , log_archive_dest_14 ,string , log_archive_dest_15 ,string , log_archive_dest_16 ,string , log_archive_dest_17 ,string , log_archive_dest_18 ,string , log_archive_dest_19 ,string , log_archive_dest_2 ,string ,service=GGSRC04T ASYNC valid_f , ,or=(ONLINE_LOGFILE,PRIMARY_ROL , ,E) db_unique_name=GGSRC04T log_archive_dest_20 ,string , log_archive_dest_21 ,string , log_archive_dest_22 ,string , log_archive_dest_23 ,string , log_archive_dest_24 ,string , log_archive_dest_25 ,string , log_archive_dest_26 ,string , log_archive_dest_27 ,string , log_archive_dest_28 ,string , log_archive_dest_29 ,string , log_archive_dest_3 ,string , log_archive_dest_30 ,string , log_archive_dest_31 ,string , log_archive_dest_4 ,string , log_archive_dest_5 ,string , log_archive_dest_6 ,string , log_archive_dest_7 ,string , log_archive_dest_8 ,string , log_archive_dest_9 ,string , log_archive_dest_state_1 ,string ,enable log_archive_dest_state_10 ,string ,enable log_archive_dest_state_11 ,string ,enable log_archive_dest_state_12 ,string ,enable log_archive_dest_state_13 ,string ,enable log_archive_dest_state_14 ,string ,enable log_archive_dest_state_15 ,string ,enable log_archive_dest_state_16 ,string ,enable log_archive_dest_state_17 ,string ,enable log_archive_dest_state_18 ,string ,enable log_archive_dest_state_19 ,string ,enable log_archive_dest_state_2 ,string ,enable log_archive_dest_state_20 ,string ,enable log_archive_dest_state_21 ,string ,enable log_archive_dest_state_22 ,string ,enable log_archive_dest_state_23 ,string ,enable log_archive_dest_state_24 ,string ,enable log_archive_dest_state_25 ,string ,enable log_archive_dest_state_26 ,string ,enable log_archive_dest_state_27 ,string ,enable log_archive_dest_state_28 ,string ,enable log_archive_dest_state_29 ,string ,enable log_archive_dest_state_3 ,string ,enable log_archive_dest_state_30 ,string ,enable log_archive_dest_state_31 ,string ,enable log_archive_dest_state_4 ,string ,enable log_archive_dest_state_5 ,string ,enable log_archive_dest_state_6 ,string ,enable log_archive_dest_state_7 ,string ,enable log_archive_dest_state_8 ,string ,enable log_archive_dest_state_9 ,string ,enable 11:44:57 SQL> select process,status,client_process,sequence# from gv$managed_standby; PROCESS ,STATUS ,CLIENT_P, SEQUENCE# ---------,------------,--------,---------- ARCH ,CONNECTED ,ARCH , 0 DGRD ,ALLOCATED ,N/A , 0 DGRD ,ALLOCATED ,N/A , 0 ARCH ,CONNECTED ,ARCH , 0 ARCH ,CLOSING ,ARCH , 53 ARCH ,CONNECTED ,ARCH , 0 RFS ,IDLE ,Archival, 0 RFS ,IDLE ,LGWR , 54 8 rows selected. Elapsed: 00:00:00.01 11:45:15 SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT; no rows selected Elapsed: 00:00:00.01 11:45:21 SQL> alter database recover automatic managed standby database; ^Calter database recover automatic managed standby database * ERROR at line 1: ORA-16043: Redo apply has been canceled. ORA-01013: user requested cancel of current operation Elapsed: 00:00:02.08 11:45:28 SQL> alter database recover automatic managed standby database disconnect; Database altered. Elapsed: 00:00:06.03 11:45:41 SQL> select process,status,client_process,sequence# from gv$managed_standby; PROCESS ,STATUS ,CLIENT_P, SEQUENCE# ---------,------------,--------,---------- ARCH ,CONNECTED ,ARCH , 0 DGRD ,ALLOCATED ,N/A , 0 DGRD ,ALLOCATED ,N/A , 0 ARCH ,CONNECTED ,ARCH , 0 ARCH ,CLOSING ,ARCH , 53 ARCH ,CONNECTED ,ARCH , 0 RFS ,IDLE ,Archival, 0 RFS ,IDLE ,LGWR , 54 MRP0 ,APPLYING_LOG,N/A , 54 9 rows selected. Elapsed: 00:00:00.00 11:45:52 SQL> select * from v$log; GROUP#, THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, MEMBERS,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME , CON_ID ----------,----------,----------,----------,----------,----------,---,----------------,-------------,--------------------,------------,--------------------,---------- 1, 1, 0, 209715200, 512, 1,YES,UNUSED , 600558,04/SEP/2023 11:05:52, 1.8447E+19, , 0 3, 1, 0, 209715200, 512, 1,YES,UNUSED , 0, , 0, , 0 2, 1, 0, 209715200, 512, 1,YES,UNUSED , 0, , 0, , 0 Elapsed: 00:00:00.01 11:46:00 SQL> select * from v$standby_log; GROUP#,DBID , THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, USED,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME ,LAST_CHANGE#,LAST_TIME , CON_ID ----------,----------------------------------------,----------,----------,----------,----------,----------,---,----------,-------------,--------------------,------------,--------------------,------------,--------------------,---------- 4,UNASSIGNED , 1, 0, 209715200, 512, 0,NO ,UNASSIGNED, , , , , , , 0 5,4198404018 , 1, 54, 209715200, 512, 1125376,YES,ACTIVE , 1267533,26/MAY/2024 11:23:40, , , 1270664,26/MAY/2024 11:46:05, 0 6,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 7,UNASSIGNED , 0, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0 Elapsed: 00:00:00.01 11:46:05 SQL> alter database recover managed standby database cancel; Database altered. Elapsed: 00:00:01.01 12:18:14 SQL> alter database open; Database altered. Elapsed: 00:00:01.36 12:18:23 SQL> set lines 1200 pages 3000 colsep , time on timing on trim on trims on alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';12:18:35 SQL> Session altered. Elapsed: 00:00:00.00 12:18:35 SQL> select name,open_mode from v$database; NAME ,OPEN_MODE ---------,-------------------- GGSRC04T ,READ ONLY Elapsed: 00:00:00.01 12:18:39 SQL> select instance_name,status,startup_time from v$instance; INSTANCE_NAME ,STATUS ,STARTUP_TIME ----------------,------------,-------------------- GGSRC04TSB1 ,OPEN ,26/MAY/2024 11:23:30 Elapsed: 00:00:00.02 12:18:43 SQL> show parameter config; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ dg_broker_config_file1 ,string ,/u01/app/oracle/product/12.2.0 , ,.1/db_1/dbs/dr1GGSRC04TSB1.dat dg_broker_config_file2 ,string ,/u01/app/oracle/product/12.2.0 , ,.1/db_1/dbs/dr2GGSRC04TSB1.dat log_archive_config ,string ,DG_CONFIG=(GGSRC04T,GGSRC04TSB , ,1) 12:18:47 SQL> show parameter log_archive_dest; NAME ,TYPE ,VALUE ------------------------------------,-----------,------------------------------ log_archive_dest ,string , log_archive_dest_1 ,string ,LOCATION=USE_DB_RECOVERY_FILE_ , ,DEST VALID_FOR=(ALL_LOGFILES,A , ,LL_ROLES) DB_UNIQUE_NAME=GGSR , ,C04TSB1 log_archive_dest_10 ,string , log_archive_dest_11 ,string , log_archive_dest_12 ,string , log_archive_dest_13 ,string , log_archive_dest_14 ,string , log_archive_dest_15 ,string , log_archive_dest_16 ,string , log_archive_dest_17 ,string , log_archive_dest_18 ,string , log_archive_dest_19 ,string , log_archive_dest_2 ,string ,service=GGSRC04T ASYNC valid_f , ,or=(ONLINE_LOGFILE,PRIMARY_ROL , ,E) db_unique_name=GGSRC04T log_archive_dest_20 ,string , log_archive_dest_21 ,string , log_archive_dest_22 ,string , log_archive_dest_23 ,string , log_archive_dest_24 ,string , log_archive_dest_25 ,string , log_archive_dest_26 ,string , log_archive_dest_27 ,string , log_archive_dest_28 ,string , log_archive_dest_29 ,string , log_archive_dest_3 ,string , log_archive_dest_30 ,string , log_archive_dest_31 ,string , log_archive_dest_4 ,string , log_archive_dest_5 ,string , log_archive_dest_6 ,string , log_archive_dest_7 ,string , log_archive_dest_8 ,string , log_archive_dest_9 ,string , log_archive_dest_state_1 ,string ,enable log_archive_dest_state_10 ,string ,enable log_archive_dest_state_11 ,string ,enable log_archive_dest_state_12 ,string ,enable log_archive_dest_state_13 ,string ,enable log_archive_dest_state_14 ,string ,enable log_archive_dest_state_15 ,string ,enable log_archive_dest_state_16 ,string ,enable log_archive_dest_state_17 ,string ,enable log_archive_dest_state_18 ,string ,enable log_archive_dest_state_19 ,string ,enable log_archive_dest_state_2 ,string ,enable log_archive_dest_state_20 ,string ,enable log_archive_dest_state_21 ,string ,enable log_archive_dest_state_22 ,string ,enable log_archive_dest_state_23 ,string ,enable log_archive_dest_state_24 ,string ,enable log_archive_dest_state_25 ,string ,enable log_archive_dest_state_26 ,string ,enable log_archive_dest_state_27 ,string ,enable log_archive_dest_state_28 ,string ,enable log_archive_dest_state_29 ,string ,enable log_archive_dest_state_3 ,string ,enable log_archive_dest_state_30 ,string ,enable log_archive_dest_state_31 ,string ,enable log_archive_dest_state_4 ,string ,enable log_archive_dest_state_5 ,string ,enable log_archive_dest_state_6 ,string ,enable log_archive_dest_state_7 ,string ,enable log_archive_dest_state_8 ,string ,enable log_archive_dest_state_9 ,string ,enable 12:18:51 SQL> select process,status,client_process,sequence# from gv$managed_standby; PROCESS ,STATUS ,CLIENT_P, SEQUENCE# ---------,------------,--------,---------- ARCH ,CONNECTED ,ARCH , 0 DGRD ,ALLOCATED ,N/A , 0 DGRD ,ALLOCATED ,N/A , 0 ARCH ,CONNECTED ,ARCH , 0 ARCH ,CLOSING ,ARCH , 53 ARCH ,CONNECTED ,ARCH , 0 RFS ,IDLE ,Archival, 0 RFS ,IDLE ,LGWR , 54 8 rows selected. Elapsed: 00:00:00.01 12:19:02 SQL> alter database recover automatic managed standby database disconnect; Database altered. Elapsed: 00:00:06.04 12:19:21 SQL> select process,status,client_process,sequence# from gv$managed_standby; PROCESS ,STATUS ,CLIENT_P, SEQUENCE# ---------,------------,--------,---------- ARCH ,CONNECTED ,ARCH , 0 DGRD ,ALLOCATED ,N/A , 0 DGRD ,ALLOCATED ,N/A , 0 ARCH ,CONNECTED ,ARCH , 0 ARCH ,CLOSING ,ARCH , 53 ARCH ,CONNECTED ,ARCH , 0 RFS ,IDLE ,Archival, 0 RFS ,IDLE ,LGWR , 54 MRP0 ,APPLYING_LOG,N/A , 54 9 rows selected. Elapsed: 00:00:00.03 12:19:25 SQL> select /*+ full(t1) */ id from user_abmr.tbl_abmr_26may t1 where id=100; ID ---------- 100 Elapsed: 00:00:00.03 12:19:42 SQL> set autotrace on 12:20:04 SQL> select /*+ full(t1) */ id from user_abmr.tbl_abmr_26may t1 where id=100; ID ---------- 100 Elapsed: 00:00:00.02 ERROR: ORA-16000: database or pluggable database open for read-only access SP2-0612: Error generating AUTOTRACE EXPLAIN report Statistics ---------------------------------------------------------- 151 recursive calls 0 db block gets 88 consistent gets 1 physical reads 0 redo size 536 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed 12:20:08 SQL> select /*+ full(t1) */ * from user_abmr.tbl_abmr_26may t1 where id=100; <<<<< the standby isnt having any corruption ID,NAME ----------,---------------------------------------------------------------------------------------------------- 100,MBJXM Elapsed: 00:00:00.09 ERROR: ORA-16000: database or pluggable database open for read-only access SP2-0612: Error generating AUTOTRACE EXPLAIN report Statistics ---------------------------------------------------------- 200 recursive calls 0 db block gets 208 consistent gets 1 physical reads 0 redo size 608 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 23 sorts (memory) 0 sorts (disk) 1 rows processed 12:20:33 SQL> |
10) flush the buffer cache
alter system flush buffer_cache;
Output:
SQL> alter system flush buffer_cache; System altered. SQL> |
11) Corrupt the block >>> SKIP
select table_name,tablespace_name from dba_tables where owner='USER_ABMR' and table_name='TBL_ABMR_26MAY';
select file_id,file_name from dba_data_files where tablespace_name='TBSPC_ABMR_26MAY';
select * from (select distinct dbms_rowid.rowid_block_number(rowid) from USER_ABMR.TBL_ABMR_26MAY where id=100);
-- block number found from above
dd of=<df path> bs=8192 seek=<blocknumber> conv=notrunc count=1 if=/dev/zero
set lines 1200 pages 3000 colsep , time on timing on trim on trims on
select * from v$database_block_corruption;
select * from v$nonlogged_block;
validate datafile <fileid>;
select * from v$database_block_corruption;
select * from v$nonlogged_block;
12) see ABMR (Automatic Block Media recovery in action)
-- Retry selecting the data
alter session set tracefile_identifier='ABMR';
alter session set statistics_level=ALL;
alter session set max_dump_file_size=UNLIMITED;
alter session set events '10046 trace name context forever, level 12';
explain plan for
select /*+ full(t1) */ * from tbl_abmr_26may t1 where id=100;
set autotrace on
select /*+ full(t1) */ * from tbl_abmr_26may t1 where id=100;
set autotrace off
End user shouldnt see any error this time, alert log should reflect the automatic block media recovery in action
Output:
SQL> set lines 1200 pages 3000 colsep , time on timing on trim on trims on 12:24:27 SQL> @?/rdbms/admin/utlxplp.sql PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 141468548 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 65 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TBL_ABMR_26MAY | 1 | 65 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=100) Note ----- - dynamic statistics used: dynamic sampling (level=2) 17 rows selected. Elapsed: 00:00:00.02 12:24:31 SQL> set autotrace on 12:24:40 SQL> select /*+ full(t1) */ * from tbl_abmr_26may t1 where id=100; ID,NAME ----------,---------------------------------------------------------------------------------------------------- 100,MBJXM Elapsed: 00:00:00.59 Execution Plan ---------------------------------------------------------- Plan hash value: 141468548 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 65 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TBL_ABMR_26MAY | 1 | 65 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=100) Note ----- - dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 9 consistent gets 6 physical reads 88 redo size 608 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 12:24:45 SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@vcentos79-oracle-ggsrc ABMR_ORA1578]$ sqlplus '/as sysdba' SQL*Plus: Release 12.2.0.1.0 Production on Sun May 26 12:40:16 2024 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID ---------- ---------- ---------- ------------------ --------- ---------- 8 131 1 0 ALL ZERO 0 SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@vcentos79-oracle-ggsrc ABMR_ORA1578]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Sun May 26 12:40:23 2024 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: GGSRC04T (DBID=4198404018) RMAN> validate datafile 8; Starting validate at 26-MAY-24 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=76 device type=DISK channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00008 name=/oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:01 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 8 OK 0 12665 12800 1280171 <<< block is recovered just fine. no more FAILED. File Name: /oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 5 Index 0 0 Other 0 130 Finished validate at 26-MAY-24 RMAN> select * from v$database_block_corruption; no rows selected RMAN> exit Recovery Manager complete. [oracle@vcentos79-oracle-ggsrc ABMR_ORA1578]$ alertlog: 2024-05-26 12:24:45.068000 +01:00 Hex dump of (file 8, block 131) in trace file /u01/app/oracle/diag/rdbms/ggsrc04t/GGSRC04T/trace/GGSRC04T_ora_12791_ABMR.trc Corrupt block relative dba: 0x02000083 (file 8, block 131) Completely zero block found during multiblock buffer read Reading datafile '/oradata/GGSRC04T/datafile/o1_mf_tbspc_ab_m564t46q_.dbf' for corruption at rdba: 0x02000083 (file 8, block 131) Reread (file 8, block 131) found same corrupt data (no logical check) Starting background process ABMR Corrupt Block Found TIME STAMP (GMT) = 05/26/2024 12:24:45 CONT = 0, TSN = 8, TSNAME = TBSPC_ABMR_26MAY RFN = 8, BLK = 131, RDBA = 33554563 OBJN = 24690, OBJD = 24690, OBJECT = TBL_ABMR_26MAY, SUBOBJECT = SEGMENT OWNER = USER_ABMR, SEGMENT TYPE = Table Segment ABMR started with pid=50, OS id=12848 Automatic block media recovery service is active. Automatic block media recovery requested for (file# 8, block# 131) Automatic block media recovery successful for (file# 8, block# 131) Automatic block media recovery successful for (file# 8, block# 131) 2024-05-26 12:33:03.161000 +01:00 |
>>> Now this feature works as expected, but.....
Observation:
ABMR interrupted the MRP in the standby!!!!!!!!!!!!1
MRP isnt able to start again failing due to ORA-00600 kcbzfc_1.
>> A restart of the standby seemed to have done the trick, the MRP is kicked off fine.
SBY is in sync with primary -- a test:
alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
insert into tbl_abmr_26may
select (10000+level) id
,dbms_random.string('U',5) name
from dual
connect by level < 1000;
commit;
SQL> select max(id) from tbl_abmr_26may; MAX(ID) ---------- 999 SQL> set lines 1200 pages 3000 colsep , time on timing on trim on trims on 12:37:02 SQL> select max(id) from tbl_abmr_26may; MAX(ID) ---------- 999 Elapsed: 00:00:00.00 12:37:04 SQL> alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS'; insert into tbl_abmr_26may select (10000+level) id ,dbms_random.string('U',5) name from dual connect by level < 1000; Session altered. Elapsed: 00:00:00.01 12:37:55 SQL> 12:37:55 2 12:37:55 3 12:37:55 4 12:37:55 5 999 rows created. Elapsed: 00:00:00.02 12:37:55 SQL> 12:37:55 SQL> commit; Commit complete. Elapsed: 00:00:00.01 12:37:58 SQL> select max(id) from tbl_abmr_26may; MAX(ID) ---------- 10999 Elapsed: 00:00:00.01 12:38:02 SQL> Standby: SQL> alter database recover automatic managed standby database disconnect; Database altered. SQL> select process,status,client_process,sequence# from gv$managed_standby; PROCESS STATUS CLIENT_P SEQUENCE# --------- ------------ -------- ---------- ARCH CONNECTED ARCH 0 DGRD ALLOCATED N/A 0 DGRD ALLOCATED N/A 0 ARCH CONNECTED ARCH 0 ARCH CLOSING ARCH 53 ARCH CONNECTED ARCH 0 RFS IDLE Archival 0 RFS IDLE LGWR 54 <<< MRP is failing 8 rows selected. SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 3221225472 bytes Fixed Size 8625856 bytes Variable Size 1442840896 bytes Database Buffers 1761607680 bytes Redo Buffers 8151040 bytes Database mounted. Database opened. <<< DB restarted SQL> alter database recover automatic managed standby database disconnect; Database altered. SQL> select process,status,client_process,sequence# from gv$managed_standby; PROCESS STATUS CLIENT_P SEQUENCE# --------- ------------ -------- ---------- ARCH CLOSING ARCH 54 DGRD ALLOCATED N/A 0 DGRD ALLOCATED N/A 0 ARCH CONNECTED ARCH 0 ARCH CONNECTED ARCH 0 ARCH CONNECTED ARCH 0 RFS IDLE Archival 0 RFS IDLE LGWR 55 MRP0 APPLYING_LOG N/A 55 <<<MRP is alive again. 9 rows selected. SQL> select max(id) from user_abmr.tbl_abmr_26may; MAX(ID) ---------- 999 SQL> set lines 1200 pages 3000 colsep , time on timing on trims on trim on 12:37:43 SQL> 1 1* select max(id) from user_abmr.tbl_abmr_26may 12:37:45 SQL> / MAX(ID) ---------- 999 Elapsed: 00:00:00.00 12:37:45 SQL> select max(id) from tbl_abmr_26may; select max(id) from tbl_abmr_26may * ERROR at line 1: ORA-00942: table or view does not exist Elapsed: 00:00:00.00 12:38:07 SQL> select max(id) from user_abmr.tbl_abmr_26may 12:38:11 2 / MAX(ID) ---------- 10999 Elapsed: 00:00:00.02 12:38:12 SQL> |
No comments:
Post a Comment