All of these prerequsites must be fulfilled before creating and using Dbvisit StandbyMP Configuration for Oracle database

1. Standby Server preparation

Standby server must contain:

• installed dbvagentmanager software

• installed Oracle Database Home software

This means that standby server must fulfill all installation prerequisites as stated in Installation Requirements and also to all Oracle Software requirements.

It is not required to create any Oracle database on standby server.

2. Oracle Home Check

Standby (DR) Oracle Home and Primary Oracle Home must be on identical patch level. To identify the patch level, you can run:

$ORACLE_HOME/OPatch/opatch lsinventory

Note that you need to have exactly same patches installed on primary and standby, not only matching release.

3. Prepare Filesystem Space

In general, you will need to have free space on primary equal to the size of the primary database. This space will be used by RMAN full backup which Dbvisit uses for standby database creation. On standby server you need to have free space equal to 2x size of your primary database (1= space for database, 1=space for backup).

SQL for size of your primary database:

sqlplus / as sysdba
SQL> select sum(bytes)/1024/1024 "Size in MB" from v$datafile;

On primary and standby server, you can pre-create directories which you will use for storing the backup during standby database creation. For example:

mkdir /u01/app/oracle/dbvisit_csd_backup

4. Server Time Check

Oracle Database time is derived from OS session time which starts up the database. Dbvisit uses Database time for calculating various variables including standby database time lag. You need to ensure that your primary and standby servers have same time and same timezone. On both serves run:

$ date
Wed Feb 14 15:35:38 CET 2024
$ sudo systemctl status chronyd #EL8 and newer
$ sudo systemctl status ntpd #EL7 and older

The different time is indication that OS time synchronization daemons (chronyd, ntpd) are not working properly or are not configured. The time difference (or timezone difference) will then cause confusing gap time report of standby database in the GUI dashboard.

5. Check Primary Database

You need to verify following on the primary database:

5.1 Database is in archivelog mode

SQL> archive log list;
Database log mode              Archive Mode
...

5.2 Database uses spfile:

SQL> sho parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/23.0.0
                                                 /dbhome_1/dbs/spfileSLASH.ora

If you don’t get file path, then your database uses pfile only and you need to create & start your primary database with spfile.

5.3 Database is in force logging mode:

SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES

Dbvisit can’t keep your standby database effectively in sync without force logging enabled. In case result of the select is “NO”, you will need to enable the force logging mode by running:

SQL> alter database force logging;

5.4 Database redo logs should use 512 byte sectors only:

SQL> select distinct(blocksize) from v$log;
 BLOCKSIZE
----------
       512

5.5 Database redo logs size

The redo on your primary DB should be at least of 200MB in size (each member):

SQL> select distinct(bytes)/1024/1024 "Redo Member Size in MB" from v$log;
Redo Member Size in MB
----------------------
                   200

Note that 200MB is the bare minimum. Usually we would recommend to have even bigger redologs, because too frequent redo log switches will cause your standby database to get out of sync. You should aim for archivelog switch to happen at most every 5 minutes (approx. 300 archlogs per day) in your “business as usual” situation, before you create any standby database.

You can use following select to determine archivelog count generated per day for past 7 days:

select to_char(trunc(l.first_time,'dd'),'yyyy-mm-dd') date_time
, count(l.recid) archivelog_count
, round(sum(l.blocks*l.block_size)/1024 /1024) MB
from v$archived_log l
where l.first_time > sysdate-7
group by to_char(trunc(l.first_time,'dd'),'yyyy-mm-dd') order by date_time desc;

If your archivelog generation is higher than required, you should increase size of your redo appropriately, otherwise the synchronization might experience delays.

6. Cleanup Standby Server

This step doesn’t apply for first-time standby database setup when standby server is clean. You can skip this step, if this is your case.

If there was any database existing on standby server (you’re for example re-creating standby database), then cleanup is necessary.

Before creating standby database, you must ensure that the standby server doesn’t contain any old database files.

• If standby server contains existing running database which you want to replace with new standby, you can use following command for cleanup:

export ORACLE_SID=<yoursid>
rman target /
STARTUP mount FORCE dba;
DROP DATABASE INCLUDING BACKUPS NOPROMPT;

As the command is very destructive, make sure to double check that you’re on correct server and ORACLE_SID is set properly.

• If there are old database files on standby server, but database can’t be started, you will have to cleanup all locations manually

7. Verify Standby Server Memory Limits

Dbvisit doesn’t configure in any way memory limits on your standby server but we need those to be correctly set before you will start standby database creation. The easiest way to verify if the memory limits are set correctly, is to startup a dummy instance on standby server.

• check your current memory allocation on primary:

SQL> sho parameter sga_max_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 6G
SQL> sho parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 6G
SQL> sho parameter pga_aggregate_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 1G

• on standby server create pfile for the dummy instance:

$ cat $ORACLE_HOME/dbs/initMEMTEST.ora
*.pga_aggregate_target=1g
*.sga_max_size=6g
*.sga_target=6g
*.db_name=MEMTEST

• attempt to start the instance:

$ export ORACLE_SID=MEMTEST
$ sqlplus / as sysdba
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 6439854152 bytes
Fixed Size                  9986120 bytes
Variable Size            6354370560 bytes
Database Buffers           67108864 bytes
Redo Buffers                8388608 bytes
SQL> shu abort;

• if you see any issues or errors, you need to refer to dummy instance alert log, for example:

$ cat /u01/app/oracle/diag/rdbms/memtest/MEMTEST/trace/alert_MEMTEST.log
2024-03-18T15:36:20.878263+01:00
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 6442450944 bytes. 
/dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. 
Current available is 4166488064 and used is 0 bytes. Ensure that the mount point is /dev/shm for this directory.

8. Check your backup scripts on Primary database

You must ensure that no archivelog on your primary database will be deleted before Dbvisit is able to send it to the standby server.

Very often RMAN backup scripts on production are set to backup archivelogs and delete all archivelogs immediately after backup:

RMAN> backup archivelog all not backed up 1 times delete input;

Such archivelog backup & retention strategy won’t be compatible with Dbvisit. Archivelog might be deleted by RMAN backups before it is shipped to standby causing the synchronization to break.

Dbvisit requires retention of at least 1-2 hours worth of archivelogs (recommendation would be 1 day if your disk space is available).

This can be achiveved for example by following commands:

RMAN> backup archivelog all not backed up 1 times;
RMAN> delete force noprompt archivelog all completed before 'sysdate -1';

this will delete all archivelogs from primary older than 1 day.