- Deploy a multicloud disaster recovery topology by using Oracle Database Service for Azure
- Deploy the Topology
Deploy the Topology
The RMAN "from service" clause enables you to restore and recover primary
database files to a standby database across the network. You can use this functionality to
instantiate a standby database in lieu of the RMAN DUPLICATE DATABASE
command and is more intuitive and less error prone, thus a time savings.
Note:
Click Copy to save the command example to your clipboard for pasting into your command line. Be sure to replace anyvariables
with values specific to your
implementation.
Create the Standby Database Using RMAN
Use Oracle Recovery Manager (RMAN) to create the standby database from the active primary database. Alternately, you can restore from a backup of the primary database.
- Connect to the database and start the database in
NOMOUNT
mode.$ rman target /
startup nomount;
- Restore the standby control file from the primary service. In this example,
DBUKS_898_LHR is the primary database:
The output should look similar to the following:restore standby controlfile from service 'DBUKS_898_LHR';
Starting restore at 04-JUL-23 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=28 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service DBUKS_898_LHR channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 output file name=+RECO/DBUKS_R2J_AMS/CONTROLFILE/current.256.1139953721 Finished restore at 04-JUL-23
- Mount the database:
The output should look similar to the following:alter database mount;
released channel: ORA_DISK_1 Statement processed
- As root on the OCI instance, change the permissions of the Oracle directory to
open (
chmod 777
).cd /opt/
ls -ltra
You should see:drwxr-xr-x 10 root root 4096 Jun 20 03:52 oracle
The enter:[root@ldbuksdr]# chmod 777 oracle/
ls -ltra
You should see:drwxrwxrwx 10 root root 4096 Jun 20 03:52 oracle
- Restore the database from the primary database (DBUKS_898_LHR):
The output should look similar to the following:restore database from service ' DBUKS_898_LHR ';
Starting restore at 04-JUL-23 using target database control file instead of recovery catalog allocated channel: ORA_SBT_TAPE_1 channel ORA_SBT_TAPE_1: SID=162 device type=SBT_TAPE channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=19.0.0.1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=28 device type=DISK channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00001 to +DATA/DBUKS_898_LHR/DATAFILE/system.261.1139943103 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service DBUKS_898_LHR channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to +DATA/DBUKS_898_LHR/DATAFILE/sysaux.268.1139943085 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:38 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00004 to +DATA/DBUKS_898_LHR/DATAFILE/undotbs1.260.1139943133 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:04 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00005 to +DATA/DBUKS_898_LHR/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/system.264.1139942759 channel ORA_DISK_1: restore complete, elapsed time: 00:00:49 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service DBUKS_898_LHR channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00006 to +DATA/DBUKS_898_LHR/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/sysaux.265.1139942759 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:25 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00007 to +DATA/DBUKS_898_LHR/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/undotbs1.266.1139942759 channel ORA_DISK_1: restore complete, elapsed time: 00:00:18 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service DBUKS_898_LHR channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00008 to +DATA/DBUKS_898_LHR/FE812140C0716067E053F6005A0AEB32/DATAFILE/system.273.1139943583 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:04 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00009 to +DATA/DBUKS_898_LHR/FE812140C0716067E053F6005A0AEB32/DATAFILE/sysaux.271.1139943599 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:16 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00010 to +DATA/DBUKS_898_LHR/FE812140C0716067E053F6005A0AEB32/DATAFILE/undotbs1.270.1139943611 channel ORA_DISK_1: restore complete, elapsed time: 00:00:20 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service DBUKS_898_LHR channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00011 to +DATA/DBUKS_898_LHR/DATAFILE/users.269.1139943873 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:02 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00012 to +DATA/DBUKS_898_LHR/FE812140C0716067E053F6005A0AEB32/DATAFILE/users.274.1139943875 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:02 Finished restore at 04-JUL-23
exit ;
Validate the Standby Database
Next, you need to validate the standby database.
- View the database name and role. From the
SQL>
prompt, enter:select FORCE_LOGGING, FLASHBACK_ON, OPEN_MODE, DATABASE_ROLE, DATAGUARD_BROKER, PROTECTION_MODE from v$database ;
You should see response similar to this:
FORCE_LOGGING FLASHBACK_ON OPEN_MODE DATABASE_ROLE DATAGUARD_BROKER PROTECTION_MODE -------------------- ------------------ --------------- ------------------- ------------------------ ---------------------------- YES NO MOUNTED PHYSICAL STANDBY DISABLED MAXIMUM PERFORMANCE
- View the archive process, status thread number, and sequence number for the
database:
select sysdate,process,status,thread#,sequence#,block# from v$managed_standby where status!='IDLE';
You should see response similar to this:
04-JUL-23 ARCH CONNECTED 0 0 0 04-JUL-23 DGRD ALLOCATED 0 0 0 04-JUL-23 DGRD ALLOCATED 0 0 0 04-JUL-23 ARCH CONNECTED 0 0 0 04-JUL-23 ARCH CONNECTED 0 0 0 04-JUL-23 ARCH CONNECTED 0 0 0
Then enter:
select distinct process from gv$managed_standby;
You should see response similar to this:
PROCESS --------- ARCH DGRD
Add Standby Logfiles to the Primary and Standby Databases
SQL>
command prompt for the following steps.
- First, add the standby logfiles to the primary database.
- Enter:
select group#, type, member from v$logfile;
You'll see a response similar to this:
GROUP# TYPE MEMBER ---------- ------- --------------------------------------------------------- 3 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_3.259.1139942665 2 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_2.258.1139942665 1 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_1.257.1139942665
- Enter:
select bytes, group# from v$log;
You'll see a response similar to this:1073741824 1 1073741824 3 1073741824 2
- Enter this series of
commands:
SQL> alter database add standby logfile thread 1 group 5 ('+RECO') size 1073741824;
alter database add standby logfile thread 1 group 6 ('+RECO') size 1073741824;
alter database add standby logfile thread 1 group 7 ('+RECO') size 1073741824;
The response for each should be:Database altered
- Enter:
select group#, type, member from v$logfile;
You'll see a response similar to this:GROUP# TYPE MEMBER ---------- ------- ------------------------------------------------------- 3 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_3.259.1139942665 2 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_2.258.1139942665 1 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_1.257.1139942665 4 STANDBY +RECO/DBUKS_898_LHR/ONLINELOG/group_4.432.1141789993 5 STANDBY +RECO/DBUKS_898_LHR/ONLINELOG/group_5.431.1141790001 6 STANDBY +RECO/DBUKS_898_LHR/ONLINELOG/group_6.430.1141790011 7 STANDBY +RECO/DBUKS_898_LHR/ONLINELOG/group_7.430.1141790017
- Archive the log list:
archive log list
- Enter:
- Now, add the standby logfiles to the standby database.
- Enter:
select group#, type, member from v$logfile;
You'll see a response similar to this:GROUP# TYPE MEMBER ---------- ------- ------------------------------------------------------ 3 ONLINE +RECO/DBUKS_R2J_AMS/ONLINELOG/group_3.489.1141280529 2 ONLINE +RECO/DBUKS_R2J_AMS/ONLINELOG/group_2.488.1141280519 1 ONLINE +RECO/DBUKS_R2J_AMS/ONLINELOG/group_1.487.1141280511
- Enter:
select bytes, group# from v$log;
You'll see a response similar to this:1073741824 1 1073741824 3 1073741824 2
- Enter this series of
commands:
alter database add standby logfile thread 1 group 4 ('+RECO') size 1073741824;
alter database add standby logfile thread 1 group 5 ('+RECO') size 1073741824;
alter database add standby logfile thread 1 group 6 ('+RECO') size 1073741824;
alter database add standby logfile thread 1 group 7 ('+RECO') size 1073741824;
The response for each command should be:Database altered
- Enter:
select group#, type, member from v$logfile;
You'll see a response similar to this:GROUP# TYPE MEMBER ---------- ------- ------------------------------------------------------ 3 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_3.489.1141280529 2 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_2.488.1141280519 1 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_1.487.1141280511 4 STANDBY +DATA/DBUKS_R2J_AMS/ONLINELOG/group_4.294.1141283919 5 STANDBY +DATA/DBUKS_R2J_AMS/ONLINELOG/group_5.295.1141283929 6 STANDBY +DATA/DBUKS_R2J_AMS/ONLINELOG/group_6.296.1141283937 7 STANDBY +DATA/DBUKS_R2J_AMS/ONLINELOG/group_7.296.1141283938
- Enter:
- Drop the logfiles with the primary database unique name. Out of the three
logfiles, one of the files will be the current log, and hence you won't be able
to drop it at this time; this file will be dropped and recreated later.
- Enter:
alter database drop logfile group 1;
You'll see a response similar to this:alter database drop logfile group 1 * ERROR at line 1: ORA-01623: log 1 is current log for instance dbuks (thread 1) - cannot drop ORA-00312: online log 1 thread 1: '+RECO/DBUKS_898_LHR/ONLINELOG/group_1.257.1139942665'
- Enter:
alter database drop logfile group 2;
alter database drop logfile group 3;
The response for each should be:Database altered
- Enter:
- Recreate the logfiles by entering the following commands:
alter database add logfile thread 1 group 2 ('+RECO') size 1073741824;
alter database add logfile thread 1 group 3 ('+RECO') size 1073741824;
The response for each should be:Database altered.
Configure the Oracle Data Guard Broker
- Check the dg_broker_start value for the
primary database and standby database. Ensure it is set to True; at the SQL
command line, enter:
show parameter dg_broker_start;
The output should look similar to the following:NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUE
If the dg_broker_start is set toFALSE
, then set the parameter toTRUE
before proceeding to the next steps; enter:alter system set dg_broker_start=true;
The output should look similar to the following:select pname from v$process where pname like 'DMON%';
PNAME ----- DMON
- Check the Oracle Data Guard files for the primary database:
show parameter dg_broker_config_file1;
If you have Oracle RAC or Oracle ASM, then you can change the configuration file location; for example:show parameter dg_broker_config_file2;
alter system set dg_broker_config_file1=broker_config_file_location;
alter system set dg_broker_config_file2=broker_config_file_location;
- Register the primary and standby databases:Use the Oracle Data Guard command-line interface (DGMGRL) to register the primary database and to add the standby database profile to the broker configuration:
- Log into the primary host as
sys.
dgmgrl sys/password@net_service_name_for_primary
- Create a configuration that uses the primary database name.
CREATE CONFIGURATION configuration_name AS PRIMARY DATABASE IS primary_database_name CONNECT IDENTIFIER IS primary_database_name;
The output should look similar to the following:CREATE CONFIGURATION dbuks_898_lhr_dbuks_r2j_ams AS PRIMARY DATABASE IS dbuks_898_lhr CONNECT IDENTIFIER IS dbuks_898_lhr;
- Add the standby database.
For example:ADD DATABASE standby unique database name AS CONNECT IDENTIFIER IS standby unique database name MAINTAINED AS PHYSICAL;
ADD DATABASE dbuks_r2j_ams AS CONNECT IDENTIFIER IS dbuks_r2j_ams MAINTAINED AS PHYSICAL;
The output should look similar to the following:Database "dbuks_r2j_ams" added
- Log into the primary host as
sys.
- Enable the configuration:
enable configuration;
- Display the configuration:
- Enter:
show configuration;
Note:
If you receive a WARNING that the apply lag could not be determined, log in to the primary database through sqlplus and do some log switches.Configuration - dbuks_898_lhr_dbuks_r2j_ams Protection Mode: MaxPerformance Members: dbuks_898_lhr - Primary database dbuks_r2j_ams - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 36 seconds ago)
- Display the configuration for the primary database (in this
example, dbuks_898_lhr):
You will see a response similar to this:show database dbuks_898_lhr
Database - dbuks_898_lhr Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): dbuks Database Status: SUCCESS
- Display the configuration for the standby database (in this
example,
dbuks_r2j_ams):
You will see a response similar to this:show database dbuks_r2j_ams
Database - dbuks_r2j_ams Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 59.00 KByte/s Real Time Query: ON Instance(s): dbuks Database Status: SUCCESS
- Enter:
Confirm the Replication
The standby database should now start applying the redo received from the primary database.
SQL>
command prompt for the following steps.
- Check the MRP process:
- Enter:
select sysdate, process, status, thread#, sequence#, block# from v$managed_standby where status!='IDLE';
The output should look similar to the following:SYSDATE PROCESS STATUS THREAD# SEQUENCE# BLOCK# --------- --------- ------------ ---------- ---------- ---------- 10-JUL-23 DGRD ALLOCATED 0 0 0 10-JUL-23 ARCH CONNECTED 0 0 0 10-JUL-23 DGRD ALLOCATED 0 0 0 10-JUL-23 ARCH CONNECTED 0 0 0 10-JUL-23 ARCH CONNECTED 0 0 0 10-JUL-23 ARCH CONNECTED 0 0 0 10-JUL-23 RFS RECEIVING 1 417 2413 10-JUL-23 MRP0 APPLYING_LOG 1 417 2412
- View the Data Guard processes.
The output should look similar to the following:select distinct process from gv$managed_standby;
PROCESS --------- DGRD RFS MRP0 ARCH
- The standby database has now started applying the redo logs.
Do some log switches on the primary database and confirm again on the
standby
database:
The output should look similar to the following:select sysdate, process, status, thread#, sequence#, block#from v$managed_standbywhere status!='IDLE';
SYSDATE PROCESS STATUS THREAD# SEQUENCE# BLOCK# --------- --------- ------------ ---------- ---------- ---------- 04-JUL-23 DGRD ALLOCATED 0 0 0 04-JUL-23 ARCH CONNECTED 0 0 0 04-JUL-23 DGRD ALLOCATED 0 0 0 04-JUL-23 ARCH CLOSING 1 253 1 04-JUL-23 ARCH CONNECTED 0 0 0 04-JUL-23 ARCH CLOSING 1 252 1 04-JUL-23 MRP0 APPLYING_LOG 1 254 17 04-JUL-23 RFS RECEIVING 1 254 18
- Archive the log list on the primary
database:
The output should look similar to the following:archive log list
Then enter:Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 415 Next log sequence to archive 417 Current log sequence 417
alter system switch logfile;
You should receive this response:System altered.
Enter:/
You should see:System altered.
Now, archive the log list:
You should receive this response:archive log list
Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 417 Next log sequence to archive 419 Current log sequence 419
- Archive the log list on the standby database;
enter:
select sysdate, process, status, thread#, sequence#, block# from v$managed_standby where status!='IDLE';
You should receive this response:
SYSDATE PROCESS STATUS THREAD# SEQUENCE# BLOCK# --------- --------- ------------ ---------- ---------- ---------- 10-JUL-23 DGRD ALLOCATED 0 0 0 10-JUL-23 ARCH CONNECTED 0 0 0 10-JUL-23 DGRD ALLOCATED 0 0 0 10-JUL-23 ARCH CLOSING 1 418 1 10-JUL-23 ARCH CONNECTED 0 0 0 10-JUL-23 ARCH CLOSING 1 417 2048 10-JUL-23 RFS RECEIVING 1 419 59 10-JUL-23 MRP0 APPLYING_LOG 1 419 58
- Enter:
- Enable Flashback on the standby database:
- Enter:
After each command, youi should see:ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel; SQL> alter database flashback on;
Database altered
- Confirm that database flashback is enabled (on).
The output should look similar to the following:select flashback_on from v$database;
FLASHBACK_ON ------------------ YES
- Then
enter:
You should see:recover managed standby database using current logfile disconnect from session;
Media recovery complete.
- Enter:
- View the database details:
The output should look similar to the following:select FORCE_LOGGING, FLASHBACK_ON, OPEN_MODE, DATABASE_ROLE, DATAGUARD_BROKER, PROTECTION_MODE from v$database ;
FORCE_LOGGING FLASHBACK_ON OPEN_MODE DATABASE_ROLE DATAGUARD_BROKER PROTECTION_MODE ---------------------------------------------------------- --------------- ----- ----------------------- ----------------------------- YES YES MOUNTED PHYSICAL STANDBY ENABLED MAXIMUM PERFORMANCE
- Use the the Server Control utility (srvctl) to stop the
standby database:
The output should look similar to this:srvctl stop database -d db_unique_name
srvctl stop database -d dbuks_r2j_ams
- Use the the Server Control utility to start the standby database:
The output should look similar to this:srvctl start database -d db_unique_name
srvctl start database -d dbuks_r2j_ams
- View the database details:
The output should look similar to this:select FORCE_LOGGING, FLASHBACK_ON, OPEN_MODE, DATABASE_ROLE, DATAGUARD_BROKER, PROTECTION_MODE from v$database ;
FORCE_LOGGING FLASHBACK_ON OPEN_MODE DATABASE_ROLE DATAGUARD_BROKER PROTECTION_MODE ----------------------------------------------------------------------- -------------------- ----------------------- YES YES READ ONLY WITH APPLY PHYSICAL STANDBY ENABLED MAXIMUM PERFORMANCE
- You can now give some more logfile switches from the primary and ensure that the redo is being applied to the standby.
- Recreate the pending logfile, which exists with the primary unique name. Enter
these commands:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
alter database drop logfile group 1;
After each command, you should see:alter database add standby logfile thread 1 group 1 ('+RECO') size 1073741824;
Finally, enter:Database altered.
You should see:recover managed standby database using current logfile disconnect from session;
Media recovery complete.