Recover Standby Database from Primary using Service in Oracle: In this article we will discuss about how to Recover Standby Database from Primary using Service in Oracle 19c.
If your standby database is out of sync from primary. Or some archive logs got deleted before it is being shipped or applied to standby. Then, you can follow below method to sync standby database with primary. This process is known as rolling forward of standby database.
Environment Details:
Check both side archive sync status:
On Primary Side:
===========================
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 151
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
DGRD ALLOCATED 0
ARCH CLOSING 149
DGRD ALLOCATED 0
ARCH CLOSING 150
ARCH CLOSING 151
ARCH CLOSING 151
LNS WRITING 152
DGRD ALLOCATED 0
LNS WRITING 152
DGRD ALLOCATED 0
10 rows selected.
SQL>
On Standby Side:
=================================
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 151
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
DGRD ALLOCATED 0
ARCH CONNECTED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 152
MRP0 WAIT_FOR_GAP 131
9 rows selected.
SQL>
Note:- Here in this case some archives were deleted from standby side before applied to standby.
Cancel recovery of standby database:
SQL> !ps -ef | grep mrp
oracle 19046 1 0 01:58 ? 00:00:00 ora_mrp0_ORCL
oracle 19064 17087 0 01:58 pts/0 00:00:00 /bin/bash -c ps -ef | grep mrp
oracle 19066 19064 0 01:58 pts/0 00:00:00 grep mrp
SQL>
SQL> alter database recover managed standby database cancel;
Database altered.
SQL>
SQL> !ps -ef | grep mrp
oracle 19088 17087 0 01:58 pts/0 00:00:00 /bin/bash -c ps -ef | grep mrp
oracle 19090 19088 0 01:58 pts/0 00:00:00 grep mrp
SQL>
Change the Standby database mode to mount state:
SQL> select name, db_unique_name, database_role, open_mode, startup_time from v$database, v$instance;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE STARTUP_T
--------- ------------------------------ ---------------- -------------------- ---------
ORCL ORCLDR PHYSICAL STANDBY READ ONLY 17-AUG-23
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 805304048 bytes
Fixed Size 9139952 bytes
Variable Size 218103808 bytes
Database Buffers 570425344 bytes
Redo Buffers 7634944 bytes
Database mounted.
SQL>
SQL> select name, db_unique_name, database_role, open_mode, startup_time from v$database, v$instance;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE STARTUP_T
--------- ------------------------------ ---------------- -------------------- ---------
ORCL ORCLDR PHYSICAL STANDBY MOUNTED 17-AUG-23
SQL>
Connect with RMAN and run Recovery command on Standby database:
[oracle@standby ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Aug 17 02:07:29 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1668087118, not open)
RMAN> recover standby database from service ORCL;
Starting recover at 17-AUG-23
using target database control file instead of recovery catalog
Oracle instance started
Total System Global Area 805304048 bytes
Fixed Size 9139952 bytes
Variable Size 218103808 bytes
Database Buffers 570425344 bytes
Redo Buffers 7634944 bytes
contents of Memory Script:
{
restore standby controlfile from service 'ORCL';
alter database mount standby database;
}
executing Memory Script
Starting restore at 17-AUG-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA/ORCLDR/CONTROLFILE/current.257.1140390057
output file name=+RECO/ORCLDR/CONTROLFILE/current.256.1140390059
Finished restore at 17-AUG-23
released channel: ORA_DISK_1
Statement processed
Executing: alter system set standby_file_management=manual
contents of Memory Script:
{
set newname for tempfile 1 to
"+DATA/ORCLDR/TEMPFILE/temp.268.1140392229";
set newname for tempfile 2 to
"+DATA/ORCLDR/FED12977B30E5A84E0539A2BA8C01412/TEMPFILE/temp.269.1140392239";
set newname for tempfile 3 to
"+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/TEMPFILE/temp.278.1140885177";
switch tempfile all;
set newname for datafile 1 to
"+DATA/ORCLDR/DATAFILE/system.266.1140391549";
set newname for datafile 3 to
"+DATA/ORCLDR/DATAFILE/sysaux.260.1140391497";
set newname for datafile 4 to
"+DATA/ORCLDR/DATAFILE/undotbs1.258.1140391495";
set newname for datafile 5 to
"+DATA/ORCLDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/system.264.1140391541";
set newname for datafile 6 to
"+DATA/ORCLDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/sysaux.261.1140391503";
set newname for datafile 7 to
"+DATA/ORCLDR/DATAFILE/users.262.1140391539";
set newname for datafile 9 to
"+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/system.265.1140391541";
set newname for datafile 10 to
"+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/sysaux.259.1140391497";
set newname for datafile 11 to
"+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/users.263.1140391539";
catalog datafilecopy "+DATA/ORCLDR/DATAFILE/system.266.1140391549",
"+DATA/ORCLDR/DATAFILE/sysaux.260.1140391497",
"+DATA/ORCLDR/DATAFILE/undotbs1.258.1140391495",
"+DATA/ORCLDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/system.264.1140391541",
"+DATA/ORCLDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/sysaux.261.1140391503",
"+DATA/ORCLDR/DATAFILE/users.262.1140391539",
"+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/system.265.1140391541",
"+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/sysaux.259.1140391497",
"+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/users.263.1140391539";
switch datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting implicit crosscheck backup at 17-AUG-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
Crosschecked 15 objects
Finished implicit crosscheck backup at 17-AUG-23
Starting implicit crosscheck copy at 17-AUG-23
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-AUG-23
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +RECO/ORCLDR/AUTOBACKUP/2023_06_25/s_1140399009.344.1140399021
File Name: +RECO/ORCLDR/AUTOBACKUP/2023_06_24/n_1140384313.260.1140392755
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_08_16/thread_1_seq_148.389.1145057135
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_08_16/thread_1_seq_149.388.1145057139
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_08_16/thread_1_seq_150.387.1145057141
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_08_16/thread_1_seq_151.386.1145057143
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_07_21/thread_1_seq_104.361.1142808737
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_07_21/thread_1_seq_105.362.1142809523
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_07_21/thread_1_seq_106.363.1142809525
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_07_21/thread_1_seq_107.364.1142809529
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_07_21/thread_1_seq_108.365.1142809673
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_07_21/thread_1_seq_109.366.1142809673
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_07_21/thread_1_seq_110.367.1142809675
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_07_02/thread_1_seq_100.357.1141139729
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_07_02/thread_1_seq_101.358.1141141103
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_07_02/thread_1_seq_102.359.1141141113
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_07_02/thread_1_seq_103.360.1141141113
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_07_01/thread_1_seq_98.355.1141083729
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_07_01/thread_1_seq_99.356.1141084345
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_30/thread_1_seq_90.345.1140879723
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_30/thread_1_seq_89.346.1140879723
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_30/thread_1_seq_88.347.1140879723
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_30/thread_1_seq_91.348.1140885237
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_30/thread_1_seq_92.349.1140885237
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_30/thread_1_seq_93.350.1140885239
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_30/thread_1_seq_95.351.1140885239
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_30/thread_1_seq_94.352.1140885241
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_30/thread_1_seq_96.353.1140885245
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_30/thread_1_seq_97.354.1140889749
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_18.257.1140395941
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_12.262.1140396001
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_14.263.1140396001
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_13.264.1140396001
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_15.265.1140396001
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_16.266.1140396001
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_17.267.1140396001
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_19.268.1140396027
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_20.269.1140396027
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_22.270.1140396029
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_21.271.1140396029
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_24.272.1140396033
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_23.273.1140396033
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_26.274.1140396037
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_25.275.1140396037
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_27.276.1140396039
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_29.277.1140396077
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_28.278.1140396077
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_31.279.1140396079
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_30.280.1140396079
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_32.281.1140396085
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_33.284.1140397063
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_34.290.1140397195
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_35.291.1140397197
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_36.292.1140397201
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_37.293.1140397205
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_38.294.1140397223
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_39.295.1140397223
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_40.296.1140397227
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_41.297.1140397227
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_42.298.1140397231
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_43.299.1140397231
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_44.300.1140397233
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_45.301.1140397233
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_46.302.1140397233
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_47.303.1140397237
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_48.304.1140397237
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_49.305.1140397239
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_50.306.1140397239
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_51.307.1140397243
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_52.308.1140397243
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_53.309.1140397245
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_54.310.1140397249
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_55.311.1140397249
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_56.312.1140397251
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_57.313.1140397251
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_58.314.1140397251
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_59.315.1140397255
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_60.316.1140397255
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_61.317.1140397255
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_62.318.1140397257
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_63.319.1140397257
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_64.320.1140397261
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_65.321.1140397261
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_66.322.1140397263
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_67.323.1140397265
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_68.324.1140397265
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_69.325.1140397265
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_70.326.1140397269
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_71.327.1140397271
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_72.328.1140397273
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_73.329.1140397273
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_74.330.1140397275
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_75.331.1140397277
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_76.332.1140397279
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_77.333.1140397279
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_78.334.1140397281
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_79.335.1140397283
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_80.336.1140397285
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_81.337.1140397285
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_82.338.1140397289
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_83.339.1140397289
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_84.340.1140397291
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_85.341.1140397291
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_86.342.1140397295
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_25/thread_1_seq_87.343.1140397295
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_24/thread_1_seq_9.258.1140390087
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_24/thread_1_seq_10.259.1140390089
File Name: +RECO/ORCLDR/ARCHIVELOG/2023_06_24/thread_1_seq_11.261.1140393465
renamed tempfile 1 to +DATA/ORCLDR/TEMPFILE/temp.268.1140392229 in control file
renamed tempfile 2 to +DATA/ORCLDR/FED12977B30E5A84E0539A2BA8C01412/TEMPFILE/temp.269.1140392239 in control file
renamed tempfile 3 to +DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/TEMPFILE/temp.278.1140885177 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
cataloged datafile copy
datafile copy file name=+DATA/ORCLDR/DATAFILE/system.266.1140391549 RECID=4 STAMP=1145066945
cataloged datafile copy
datafile copy file name=+DATA/ORCLDR/DATAFILE/sysaux.260.1140391497 RECID=5 STAMP=1145066945
cataloged datafile copy
datafile copy file name=+DATA/ORCLDR/DATAFILE/undotbs1.258.1140391495 RECID=6 STAMP=1145066946
cataloged datafile copy
datafile copy file name=+DATA/ORCLDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/system.264.1140391541 RECID=7 STAMP=1145066946
cataloged datafile copy
datafile copy file name=+DATA/ORCLDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/sysaux.261.1140391503 RECID=8 STAMP=1145066946
cataloged datafile copy
datafile copy file name=+DATA/ORCLDR/DATAFILE/users.262.1140391539 RECID=9 STAMP=1145066946
cataloged datafile copy
datafile copy file name=+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/system.265.1140391541 RECID=10 STAMP=1145066946
cataloged datafile copy
datafile copy file name=+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/sysaux.259.1140391497 RECID=11 STAMP=1145066946
cataloged datafile copy
datafile copy file name=+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/users.263.1140391539 RECID=12 STAMP=1145066946
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1145066945 file name=+DATA/ORCLDR/DATAFILE/system.266.1140391549
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1145066945 file name=+DATA/ORCLDR/DATAFILE/sysaux.260.1140391497
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1145066946 file name=+DATA/ORCLDR/DATAFILE/undotbs1.258.1140391495
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1145066946 file name=+DATA/ORCLDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/system.264.1140391541
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1145066946 file name=+DATA/ORCLDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/sysaux.261.1140391503
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1145066946 file name=+DATA/ORCLDR/DATAFILE/users.262.1140391539
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=1145066946 file name=+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/system.265.1140391541
datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP=1145066946 file name=+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/sysaux.259.1140391497
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=1145066946 file name=+DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/users.263.1140391539
Executing: alter database rename file '+DATA/ORCL/ONLINELOG/group_1.264.1140306383' to '+DATA/ORCLDR/ONLINELOG/group_1.271.1140397041'
Executing: alter database rename file '+RECO/ORCL/ONLINELOG/group_1.262.1140306401' to '+RECO/ORCLDR/ONLINELOG/group_1.282.1140397041'
Executing: alter database rename file '+DATA/ORCL/ONLINELOG/group_2.263.1140306383' to '+DATA/ORCLDR/ONLINELOG/group_2.272.1140397053'
Executing: alter database rename file '+RECO/ORCL/ONLINELOG/group_2.264.1140306403' to '+RECO/ORCLDR/ONLINELOG/group_2.283.1140397055'
Executing: alter database rename file '+DATA/ORCL/ONLINELOG/group_3.262.1140306383' to '+DATA/ORCLDR/ONLINELOG/group_3.273.1140397085'
Executing: alter database rename file '+RECO/ORCL/ONLINELOG/group_3.263.1140306403' to '+RECO/ORCLDR/ONLINELOG/group_3.285.1140397087'
contents of Memory Script:
{
recover database from service 'ORCL';
}
executing Memory Script
Starting recover at 17-AUG-23
using channel ORA_DISK_1
skipping datafile 5; already restored to SCN 2139755
skipping datafile 6; already restored to SCN 2139755
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL
destination for restore of datafile 00001: +DATA/ORCLDR/DATAFILE/system.266.1140391549
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL
destination for restore of datafile 00003: +DATA/ORCLDR/DATAFILE/sysaux.260.1140391497
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL
destination for restore of datafile 00004: +DATA/ORCLDR/DATAFILE/undotbs1.258.1140391495
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL
destination for restore of datafile 00007: +DATA/ORCLDR/DATAFILE/users.262.1140391539
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL
destination for restore of datafile 00009: +DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/system.265.1140391541
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL
destination for restore of datafile 00010: +DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/sysaux.259.1140391497
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL
destination for restore of datafile 00011: +DATA/ORCLDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/users.263.1140391539
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-AUG-23
Executing: alter system set standby_file_management=auto
Finished recover at 17-AUG-23
RMAN>
Recover the standby database upto consistent mode:
SQL> select name, db_unique_name, database_role, open_mode, startup_time from v$database, v$instance;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE STARTUP_T
--------- ------------------------------ ---------------- -------------------- ---------
ORCL ORCLDR PHYSICAL STANDBY MOUNTED 17-AUG-23
SQL>
SQL> alter database recover managed standby database until consistent;
Database altered.
SQL>
Note:-> If the above command is hung and taking long time to complete, then execute below command from primary database.
SQL> alter system switch logfile;
System altered.
SQL>
SQL> alter database open read only;
Database altered.
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
SQL> select name, db_unique_name, database_role, open_mode, startup_time from v$database, v$instance;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE STARTUP_T
--------- ------------------------------ ---------------- -------------------- ---------
ORCL ORCLDR PHYSICAL STANDBY READ ONLY WITH APPLY 17-AUG-23
SQL>But at this stage, ADG (Active Data Guard) features real time changes on the primary will not be reflected on standby database. So we need to recreate the standby redologs on standby database for ADG (Active Data Guard).
Recreate standby redologs on Standby database:
SQL> set lines 400 pages 40000
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
3 ONLINE +DATA/ORCLDR/ONLINELOG/group_3.273.1140397085 NO 0
3 ONLINE +RECO/ORCLDR/ONLINELOG/group_3.285.1140397087 YES 0
2 ONLINE +DATA/ORCLDR/ONLINELOG/group_2.272.1140397053 NO 0
2 ONLINE +RECO/ORCLDR/ONLINELOG/group_2.283.1140397055 YES 0
1 ONLINE +DATA/ORCLDR/ONLINELOG/group_1.271.1140397041 NO 0
1 ONLINE +RECO/ORCLDR/ONLINELOG/group_1.282.1140397041 YES 0
4 STANDBY +DATA/ORCL/ONLINELOG/group_4.274.1140309493 NO 0
4 STANDBY +RECO/ORCL/ONLINELOG/group_4.267.1140309493 NO 0
5 STANDBY +DATA/ORCL/ONLINELOG/group_5.275.1140309505 NO 0
5 STANDBY +RECO/ORCL/ONLINELOG/group_5.268.1140309507 NO 0
6 STANDBY +DATA/ORCL/ONLINELOG/group_6.276.1140309515 NO 0
6 STANDBY +RECO/ORCL/ONLINELOG/group_6.269.1140309515 NO 0
7 STANDBY +DATA/ORCL/ONLINELOG/group_7.277.1140309523 NO 0
7 STANDBY +RECO/ORCL/ONLINELOG/group_7.270.1140309523 NO 0
14 rows selected.
SQL> select GROUP#, THREAD#, BYTES/1024/1024 from v$standby_log;
GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
4 0 200
5 0 200
6 0 200
7 0 200
SQL>
SQL> alter database recover managed standby database cancel;
Database altered.
------------------------ Dropping standby redologs ---------------------------
SQL> alter database drop standby logfile group 4;
Database altered.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
------------------------ Adding standby redologs ---------------------------
SQL> alter database add standby logfile group 4 ('+DATA','+RECO') size 200m;
Database altered.
SQL> alter database add standby logfile group 4 ('+DATA','+RECO') size 200m;
Database altered.
SQL> alter database add standby logfile group 4 ('+DATA','+RECO') size 200m;
Database altered.
SQL> alter database add standby logfile group 4 ('+DATA','+RECO') size 200m;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
3 ONLINE +DATA/ORCLDR/ONLINELOG/group_3.273.1140397085 NO 0
3 ONLINE +RECO/ORCLDR/ONLINELOG/group_3.285.1140397087 YES 0
2 ONLINE +DATA/ORCLDR/ONLINELOG/group_2.272.1140397053 NO 0
2 ONLINE +RECO/ORCLDR/ONLINELOG/group_2.283.1140397055 YES 0
1 ONLINE +DATA/ORCLDR/ONLINELOG/group_1.271.1140397041 NO 0
1 ONLINE +RECO/ORCLDR/ONLINELOG/group_1.282.1140397041 YES 0
4 STANDBY +DATA/ORCLDR/ONLINELOG/group_4.279.1145068273 NO 0
4 STANDBY +RECO/ORCLDR/ONLINELOG/group_4.381.1145068275 YES 0
5 STANDBY +DATA/ORCLDR/ONLINELOG/group_5.280.1145068333 NO 0
5 STANDBY +RECO/ORCLDR/ONLINELOG/group_5.380.1145068335 YES 0
6 STANDBY +DATA/ORCLDR/ONLINELOG/group_6.281.1145068363 NO 0
6 STANDBY +RECO/ORCLDR/ONLINELOG/group_6.379.1145068365 YES 0
7 STANDBY +DATA/ORCLDR/ONLINELOG/group_7.282.1145068369 NO 0
7 STANDBY +RECO/ORCLDR/ONLINELOG/group_7.378.1145068369 YES 0
14 rows selected.
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
SQL> select name, db_unique_name, database_role, open_mode, startup_time from v$database, v$instance;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE STARTUP_T
--------- ------------------------------ ---------------- -------------------- ---------
ORCL ORCLDR PHYSICAL STANDBY READ ONLY WITH APPLY 17-AUG-23
SQL>Now on Primary database all real time changes will be reflected on Standby database.
Oracle 19c 2 node RAC to single instance standby database setup
Physical Standby Database creation Using RMAN Backup and Restore
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.
Excellent, thanks you. (I don’t speak english)