########Sync the Physical Standby Using RECOVER FROM SERVICE in Oracle
12c/19c##################
Oracle Introduce new commands RECOVER FROM SERVICE for make the database sync if
dataguard out of sync by reducing the steps.
Following steps done for Recovery the Standby database to make them sync with
RECOVER .. FROM SERVICE Command:
1. Identified the gap between both Primary and Standby Site:
Run the following command on both primary and standby DB to identified the out of
sync.
-- On Standby and primary both
-- Check SCN difference of DB
set num_width 30
SELECT CURRENT_SCN FROM V$DATABASE;
alter session set NLS_DATE_FORMAT='DD-MON-YY HH:MI:SS';
select SCN_TO_TIMESTAMP(1859336) from dual;
CREATE USER TEST1 IDENTIFIED BY TEST DEFAULT TABLESPACE "USERS" TEMPORARY
TABLESPACE "TEMP";
ALTER USER TEST1 QUOTA UNLIMITED ON USERs;
GRANT CONNECT TO TEST1;
GRANT RESOURCE TO TEST1;
create table TEST1.DEMO ("id" number , "text" varchar2(15), "number" number) ;
declare
type people_array is varray(12) of varchar(15);
people people_array := people_array( 'Marc', 'Bill', 'George', 'Eliot', 'Matt',
'Trey', 'Tracy','Greg', 'Steve', 'Kristina', 'Katie', 'Jeff' );
people_count number :=people.COUNT;
n number;
begin
for i in 0..1e6 loop
n:=trunc(dbms_random.value(0,100000));
insert into TEST1.DEMO values( i , people( dbms_random.value(1,people_count) ) ,
n );
end loop;
commit;
end;
/
select count(0) from TEST1.DEMO;
On primary :
select max(sequence#) from v$archived_log where archived='YES';
On Standby:
select max(sequence#) from v$archived_log where applied='YES';
--Check SCN difference of Files
select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
On Standby DB:
1. Stop the replication service MRP on Standby Database:
-- On Standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2. Shutdown the database and open in mount state:
-- On Standby
Shutdown immediate
Startup mount
3. Connect with the Standby Server through RMAN
-- On Standby
rman target user/pwd
4. After connected to Standby Start the recover command:
Note: Verified the Service name with tnsping command go to primary Server.
--On Standby with Tnsnames of Primary DB
RMAN> recover database from service TEST_PRIMARY noredo using compressed backupset;
5. After completion, check the SCN for the files for both production and Standby
Server:
-- On Both Primary and Standby
--Check SCN difference of Files, it should be equal.
select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
6. Now SCN number of control file is lower than Primary DB, for sync them open
standby in nomount state.
--On Standby
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
7. Restore standby control file at nomount stage by using service PRIMARY.
-- On Standby
-- RESTORE STANDBY CONTROLFILE FROM SERVICE ;
RMAN> restore standby controlfile from service TEST_PRIMARY;
8. After restore control file, Verify the path is same as primary control file as
present in Standby Server:
-- On Standby
alter database mount;
--Verfied the path of data files
report schema;
9. If location/name of data file is different in Primary than standby. If same
location then skip this steps.
If path is different then you have to catalog new location and run SWITCH command
for update location.
Note: For different path, Use the CATALOG command and the SWITCH command to update
all the data file names.
-- On Standby
-- catalog start with '';
RMAN> Catalog start with 'data file location';
-- Fixes the location
RMAN> list incarnation
RMAN> reset database to incarnation 2;
database reset to incarnation 2
RMAN>
RMAN> list incarnation;
SWITCH DATABASE TO COPY;
10. If any data files is added after upgrade in Primary then you have to replicate
that.
If not then skip this step.
-- Check the last SCN changes happen at DB on STep 5
SELECT file# FROM V$DATAFILE WHERE creation_change# >= 1984232;
11. If you need to rename the redo log files then use following commands:
select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;
ALTER DATABASE CLEAR LOGFILE GROUP 4;
ALTER DATABASE CLEAR LOGFILE GROUP 5;
ALTER DATABASE CLEAR LOGFILE GROUP 6;
ALTER DATABASE CLEAR LOGFILE GROUP 7;
ALTER DATABASE CLEAR command to clear the log files in all redo log groups of the
standby database.
alter system set standby_file_management=MANUAL;
ALTER DATABASE RENAME FILE command to rename the redo log files.
select member from v$logfile;
alter system set standby_file_management=MANUAL;
alter database rename file '/u02/app/oracle/oradata/TEST/redo01.log' to
'/u02/app/oracle/oradata/TESTSTBY/onlinelog/redo01.log';
alter database rename file '/u02/app/oracle/oradata/TEST/redo02.log' to
'/u02/app/oracle/oradata/TESTSTBY/onlinelog/redo02.log';
alter database rename file '/u02/app/oracle/oradata/TEST/redo03.log' to
'/u02/app/oracle/oradata/TESTSTBY/onlinelog/redo03.log';
alter database rename file '/u01/app/oracle/oradata/TEST/STANDBY/stby_log01.log' to
'/u02/app/oracle/oradata/TESTSTBY/onlinelog/stby_log01.log';
alter database rename file '/u01/app/oracle/oradata/TEST/STANDBY/stby_log02.log' to
'/u02/app/oracle/oradata/TESTSTBY/onlinelog/stby_log02.log';
alter database rename file '/u01/app/oracle/oradata/TEST/STANDBY/stby_log03.log' to
'/u02/app/oracle/oradata/TESTSTBY/onlinelog/stby_log03.log';
alter database rename file '/u01/app/oracle/oradata/TEST/STANDBY/stby_log04.log' to
'/u02/app/oracle/oradata/TESTSTBY/onlinelog/stby_log04.log';
select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;
ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 4;
ALTER DATABASE CLEAR LOGFILE GROUP 5;
ALTER DATABASE CLEAR LOGFILE GROUP 6;
ALTER DATABASE CLEAR LOGFILE GROUP 7;
alter system set standby_file_management=AUTO;
12. Start the recover process at standby database
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
select count(0) from TEST1.DEMO;
##############################################
On primary :
select max(sequence#) from v$archived_log where archived='YES';
On Standby:
select max(sequence#) from v$archived_log where applied='YES';
