On a DG environment, a datafile needs to be recovered on the STANDBY site, in two situations : when is deleted or corrupted.
Below, I will explain how to recover a corrupted datafile, in order to be able to repair the Standby database, without to be necessary to restore entire database.
Initial situation :
DGMGRL> connect / Connected to "PROD_SITE2" Connected as SYSDG. DGMGRL> show configuration; Configuration - CONFIG1 Protection Mode: MaxPerformance Members: PROD_SITE2 - Primary database PROD_SITE1 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 15 seconds ago)
On this environment, we have a table called EMP with 100 rows, owned by the user TEST (default tablespace TEST).
SQL> set linesize 220; SQL> select username,default_tablespace from dba_users where username='TEST'; USERNAME DEFAULT_TABLESPACE ------------------------------- TEST TEST SQL> select count(*) from test.emp; COUNT(*) ---------- 100
By mistake, the datafile on Standby site, get corrupted.
SQL> alter database open read only; alter database open read only * ORA-01578: ORACLE data block corrupted (file # 5, block # 3) ORA-01110: data file 5: '/u02/oradata/PROD/test.dbf'
As is corrupted, the apply of the redo log is stopped until will be repaired. So the new inserts into the EMP table will not be applied:
SQL> begin 2 for i in 101..150 loop 3 insert into test.emp values (i); 4 end loop; 5 END; 6 / PL/SQL procedure successfully completed. SQL> COMMIT; Commit complete. SQL> select count(*) from test.emp; COUNT(*) ---------- 150 SQL> select name,db_unique_name,database_role from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE --------- ------------------------------ ---------------- PROD PROD_SITE2 PRIMARY
To repair it, we will use PRIMARY site to backup controlfile and the related datafile.
[email protected]:/home/oracle/ [PROD] rman target / connected to target database: PROD (DBID=410572245) RMAN> backup current controlfile for standby format '/u02/backupctrl.ctl'; RMAN> backup datafile 5 format '/u02/testbkp.dbf'; Starting backup at 29-JAN-2019 10:59:37 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=276 device type=DISK
We will transfer the backuppieces on the STANDBY server, using scp:
scp backupctrl.ctl [email protected]:/u02/ scp testbkp.dbf [email protected]:/u02/
Now, will start the restore/recover on the STANDBY server :
SQL> startup nomount ORACLE instance started. Total System Global Area 1895825408 bytes Fixed Size 8622048 bytes Variable Size 570425376 bytes Database Buffers 1308622848 bytes Redo Buffers 8155136 bytes SQL> exit [email protected]:/u02/oradata/PROD/ [PROD] rman target / Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: PROD (not mounted) RMAN> restore controlfile from '/u02/backupctrl.ctl'; ......... RMAN> alter database mount; RMAN> catalog start with '/u02/testbkp.dbf'; searching for all files that match the pattern /u02/testbkp.dbf List of Files Unknown to the Database ===================================== File Name: /u02/testbkp.dbf Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u02/testbkp.dbf RMAN> restore datafile 5; Starting restore at 29-JAN-2019 11:06:31 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00005 to /u02/oradata/PROD/test.dbf channel ORA_DISK_1: reading from backup piece /u02/testbkp.dbf channel ORA_DISK_1: piece handle=/u02/testbkp.dbf tag=TAG20190129T105938 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 29-JAN-2019 11:06:33 RMAN> exit
Now, we will start to apply the logs again and try to resync the STANDBY database.
!!! Here you need to stop recovery process if you do not have a dataguard active license.
SQL> recover managed standby database using current logfile disconnect from session; Media recovery complete. SQL> recover managed standby database cancel; SQL> alter database open read only; Database altered. SQL> select count(*) from test.emp; COUNT(*) ---------- 150
Now, we can see the last insert activity on the PRIMARY site that is available on the STANDBY site.
On 12c environment, with an existing container PDB1, the things are easier, with the feature RESTORE/RECOVER from service :
connect on the standby site rman target / restore tablespace PDB1:USERS from service PROD_PRIMARY; recover tablespace PDB1:USERS;