This writing is about Dataguard technology which is high accessibility and disaster recovery solution of Oracle, also we’ll install physical Dataguard with RMAN Duplicate method.
What Is Dataguard?
Dataguard is defined as a standby server that aims to minimise data loss and interruption time during database mistakes and disasters. Dataguard is divided into two as physical and logical. Logical Dataguard is processing the logs from primary database as SQL clauses and Physical Dataguard is creating a copy of the primary database by processing the logs with recovery sense.
If you’re going to use the Dataguard server as in the way it can quickly replace primary database during a disaster, server should be able to carry the burden of the database. If you’ve lost access to primary database due to any reason or had a disaster and you need to continue system sustainability, the bond between standby server and primary server is broken with a process called Failover. Upon the necessary integrations, this server continues to serve as the primary server. To test functionality and wellness of the standby server during possible disasters, Switchover is applied. In this process, primary server and standby server replace each other by changing roles. If your point of using Dataguard is data security or backup, you can use it with relatively low system features.
Oracle Dataguard can be used in 3 different modes:
Maximum Protection
Maximum Utility
Maximum Performance (Default)
We’ll talk about these modes and Switchover & Failover processes in the coming posts in detail. Now, I’ll explain the installation step by step.
Inventory
Here is the inventory information I’ll install for the two servers. Single Instance primary database and database software should be an installed Dataguard server.
Primary Database Server
You may look at our post “Installing Oracle 19c Single Instance Database”.
Hostname: data4tech.localdomain
IP: 192.168.11.128
Operating System: Oracle Linux 7.8
Database Version: 19.3.0.0
Grid: NONE
DB_NAME: orcl
DB_UNIQUE_NAME: orcl
SID: orcl
Secondary & Standby Database Server
You may look at our post “Installing Oracle 19c Single Instance Database”. You should move till the fourth title of this writing and not create the database.
Hostname: data4techdg.localdomain
IP: 192.168.11.130
Operating System: Oracle Linux 7.8
Database Version: 19.3.0.0
Grid: NONE
DB_NAME: orcl
DB_UNIQUE_NAME: orcldg
SID: orcldg
1- Configurations in the Primary Database Server
Add the standby server information to the hosts file.
# vi /etc/hosts
To install Dataguard, primary database should be in “Archivelog” and “Force Logging” modes. Check the log mode.
SQL> select log_mode from v$database;If it isn’t on, to turn on Archivelog mode switch the database to “Mount” mode.
SQL> shutdown immediate;
SQL> startup mount;
Turn on Archivelog mode and restart the database and do “force logging”.
SQL> alter database archivelog;
SQL> alter database open;
SQL> select log_mode from v$database;
SQL> alter database force logging;
Set the database parameters necessary for the Dataguard configuration in the primary part.
SQL> alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)';
SQL> alter system set log_archive_dest_2='SERVICE=orcldg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg';
SQL> alter system set log_archive_dest_state_2=enable;
Upon setting the necessary parameters, shutdown and restart the database.
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> alter system set log_archive_max_processes=30;
SQL> alter system set fal_server='orcldg' scope=both;
SQL> alter system set fal_client='orcl' scope=both;
SQL> alter system set standby_file_management=auto scope=both;
SQL> shutdown immediate;
SQL> startup;
The views are as below where you can find information of index, size and group relating to Redolog files. As you see, standby redologs aren’t created yet.
SQL> select * from v$log;
SQL> select * from v$standby_log;
SQL> select * from v$logfile;
Create the standby redolog files to the same index, same size and as one more in number with the primary redolog files. It isn’t obligatory to create the standby redologs in the primary part but should be added to prevent any mistake during a possible Switchover process.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo01.log') SIZE 209715200;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo02.log') SIZE 209715200;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo03.log') SIZE 209715200;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo04.log') SIZE 209715200;
See that standby redologs are added.
SQL> select * from v$logfile;
Dataguard information are added to “tnsnames.ora” file.
$ vi $ORACLE_HOME/network/admin/tnsnames.oraLISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = data4tech.localdomain)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = data4tech.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = data4techdg.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
)
)
2- Configurations in the Standby Database Server
Add the information of the two servers to the hosts file.
# vi /etc/hosts
Adjust the “tnsnames.ora” file.
$ vi $ORACLE_HOME/network/admin/tnsnames.oraORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = data4tech.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = data4techdg.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
)
)
Adjust the “listener.ora” file.
$ vi $ORACLE_HOME/network/admin/listener.oraLISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = data4techdg.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcldg)
(ORACLE_HOME = /u01/app/oracle/product/19/db)
(SID_NAME = orcldg)
)
)
Start the Listener, it’s normal that ‘orcldg’ service situation is UNKNOWN.
$ lsnrctl start
Create the index in Dataguard side where Audit files will go to. Copy the password file in accordance with SID from the primary side to here. Create the “init” file. Since this file will be used only for awaking an instance, enter enough parameter.
$ mkdir -p /u01/app/oracle/admin/orcldg/adump
$ scp oracle@data4tech:$ORACLE_HOME/dbs/orapworcl /u01/app/oracle/product/19/db/dbs/orapworcldg
$ vi $ORACLE_HOME/dbs/initorcldg.oradb_name='orcl'
db_unique_name='orcldg'
db_block_size=8192
Adjust the duplicate script. You may give the script for RMAN from primary or secondary server, I’ll use secondary.
$ vi duplicate.shrun{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby1 type disk;
allocate auxiliary channel stby2 type disk;
duplicate target database for standby from active database dorecover NOFILENAMECHECK
spfile
parameter_value_convert 'ORCL','ORCLDG'
set audit_file_dest='/u01/app/oracle/admin/orcldg/adump'
set db_unique_name='orcldg'
set log_archive_max_processes='10'
set fal_server='orcl'
set standby_file_management='AUTO'
set log_archive_config='DG_CONFIG=(orcl,orcldg)'
set log_archive_dest_2='SERVICE=orcl LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
set db_file_name_convert='/u01/app/oracle/oradata/ORCL', '/u01/app/oracle/oradata/ORCLDG'
set log_file_name_convert='/u01/app/oracle/oradata/ORCL', '/u01/app/oracle/oradata/ORCLDG'
;
}
Connect to SQLPlus and turn on Instance Nomount mode.
$ sqlplus / as sysdba
SQL> startup nomount;
3- Controls Before Duplication and What to Do After
Now, both of your servers are ready for duplication process. It’s time to do the connection and tnsping tests and run the duplicate script.
Do the tnsping tests in the secondary server both with yourself and the other side.
$ tnsping orcl
$ tnsping orcldg
Do the connection tests in the secondary server both with yourself and the other side. Enter password of your own SYS user to where I wrote <PAROLA>.
$ sqlplus /nolog
SQL> connect sys/<PAROLA>@orcldg as sysdba
If you encounter ORA-12528 fault as above, apply the Workaround below. Add the (UR=A) lines to tnsnames.ora file in both servers to solve it.
$ vi $ORACLE_HOME/network/admin/tnsnames.ora...
(UR = A)
...
Let’s get back to the connection test.
$ sqlplus /nolog
SQL> connect sys/<PAROLA>@orcldg as sysdba
Connected.
SQL> exit
$
$ sqlplus /nolog
SQL> connect sys/<PAROLA>@orcl as sysdba
Connected.
SQL> exit
Do the tnsping tests in the primary.
$ tnsping orcl
$ tnsping orcldg
Do the connection tests in the primary.
$ sqlplus /nolog
SQL> connect sys/<PAROLA>@orcl as sysdba
Connected.
SQL> exit
$
$ sqlplus /nolog
SQL> connect sys/<PAROLA>@orcldg as sysdba
Connected.
SQL> exit
Upon successfully doing the necessary tests, start the duplicate script through secondary database.
Caution: If you encounter a fault during Duplicate.sh, upon solving it, turn off the instance in the secondary with the ‘shutdown abort’ command. Delete the created spfile and datafiles if a part of it is created. Give duplicate.sh after turning on the Nomount mode.
Give the script with nohup and follow its output from the log file with the tail command.
$ nohup rman target sys/<PAROLA>@orcl auxiliary sys/<PAROLA>@orcldg < duplicate.sh > duplicate.log &
$
$ tail -f duplicate.log
Upon completing Duplicate process, open the database in “Read Only” mode and start the recovery process to process logs in the secondary side synchronously. You can see the lag time with below inquiry.
$ sqlplus / as sysdba
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> set lines 200 pages 200
SQL> select name, value, time_computed from v$dataguard_stats where name like '%lag';
Now, a real time running Dataguard server is ready.
The “v$managed_standby” view will give us important clues about Dataguard. MRP is the process that processes the logs. RFS is the process that sends the archived logs. Thread tells which instance the file that is sent comes from, if you’re using RAC you can see as many Threads as the node number. What is studied on the block column is block number and sequence is file order.
SQL> select process,status,thread#,sequence#,block#,blocks from v$managed_standby order by 1;
What to understand here?
We see that the block RFS sends and the block MRP processes are in the same sequence so there isn’t a lag in this database and MRP is running.
If RFS is ahead of MRP and MRP is in the “APPLYING_LOG” mode, it cannot do the process in the secondary so you may use parallel in this situation.
If MRP is ahead of RFS, we see that Network reliant transfer is slow.
If RFS is running but MRP says “WAIT_FOR_GAP” or “WAIT_FOR_LOG”, we see that MRP couldn’t find the Archivelog it will process. These packages may be disrupted or deleted for other reasons during transfer. You can detect related archivelog and transfer them to the secondary manually.
You may use the “v$managed_standby” view to follow processing of Archivelogs.
SQL> select sequence#, first_time, next_time, applied from v$archived_log order by 1;
To see running of Dataguard, let’s do a simple application. Create a table in the primary database, add one record and commit it.
$ sqlplus / as sysdba
SQL> create table hr.deneme (id number, name varchar2(20));
SQL> insert into hr.deneme values (1, 'alperen');
SQL> commit;
You can see the table and the record in the secondary but if you try to process DML, you will encounter a fault because “Read Only” mode is on.
$ sqlplus / as sysdba
SQL> select * from hr.deneme;
To prevent problems relating to accumulation of archivelogs in long term, you should configure RMAN in both sides.
Configure as below to make the archivelogs obsolete that are sent to all standby servers and held as 2 copies in the primary database.
$ rman target /
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY BACKED UP 2 TIMES TO DISK;
Configure as below to make the archivelogs obsolete that are processed in all standby servers in the secondary database.
$ rman target /
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
4EXTRA-Creating and Managing the FRA Space in the Secondary Server
Create the “FRA” file to a separate disk or determined index.
$ cd /u01
$ mkdir FRA
Set the size and index of determined “FRA” space in the secondary database. Then, switch the logs in the primary database. We can see the archivelogs coming when the index of the related date is entered in the “FRA” space again.
Secondary Database:
$ sqlplus / as sysdba
SQL> alter system set db_recovery_file_dest_size=5G;
SQL> alter system set db_recovery_file_dest='/u01/FRA';Primary Database:
$sqlplus / as sysdba
SQL> alter system switch logfile;
SQL> r
SQL> rSecondary Database:
$ ll -lrth /u01/FRA/ORCLDG/archivelog/2021_12_13
You may use “v$recovery_area_usage” view to see distribution of the “FRA” space and manage it.
$ sqlplus / as sysdba
SQL> select * from v$recovery_area_usage;
As you see above, Archivelogs comprise 0.24% of FRA and total file number is 3. You can see the percent of the archivelogs that become obsolete according to RMAN deletion policy on the column “PERCENT_SPACE_RECLAIMABLE”. Obsolete archivelogs will be deleted automatically if there isn’t enough space. It’s important to manage this space well to prevent Dataguard from stopping or shutting down.
Full used space or no reclaimable space will result in lag due to archivelogs can’t coming. In this case, you can enlarge the space for “FRA”.
On the other hand, if the used space is 80% and reclaimable space is 60% there is no problem. This may mean that the space of the used archivelogs is 20%.
Hope to see you in new posts,
Take care.
Comments