Create Single Instance Standby Using DBCA [12.2 Version]
Tags: oracle, dbca, tutorial, standby
Published: 2020-08-27 12:55:37

Since 12.2, dbca can create Standby Databases directly, but with some restrictions, like only for Single Instance Databases and only non container Databases.

This has changed for each version, and since 18c, you can create the RAC Standby and they can have PDBs (will have an article about that soon :) )

For this test, we are going to create a single instance Database as primary called **primary** in node rac2-node1 and a single instance Standby database called **standby** in node rac2-node2 (very original names.. I know..)

First thing we need is to create the files destination on both hosts:

```bash
[oracle@rac2-node1 ~]$ . oraenv
ORACLE_SID = [oracle] ? primary
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.2.0/dbhome_1
The Oracle base has been set to /u01/app/oracle
[oracle@rac2-node1 ~]$ 

[oracle@rac2-node1 ~]$ mkdir /u01/app/oracle/oradata/
[oracle@rac2-node1 ~]$ 


[oracle@rac2-node2 ~]$ . oraenv
ORACLE_SID = [oracle] ? standby
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.2.0/dbhome_1
The Oracle base has been set to /u01/app/oracle
[oracle@rac2-node2 ~]$ mkdir /u01/app/oracle/oradata/
[oracle@rac2-node2 ~]$ 
```

We can now create our primary Database using DBCA silent mode
```bash
[oracle@rac2-node1 ~]$ dbca -silent -createDatabase \
> -templateName General_Purpose.dbc \
> -gdbname primary -sid primary -responseFile NO_VALUE \
> -characterSet AL32UTF8 \
> -sysPassword Welcome1 \
> -systemPassword Welcome1 \
> -createAsContainerDatabase false \
> -databaseType MULTIPURPOSE \
> -memoryMgmtType auto_sga \
> -totalMemory 1536 \
> -storageType FS \
> -datafileDestination "/u01/app/oracle/oradata/" \
> -redoLogFileSize 50 \
> -emConfiguration NONE \
> -ignorePreReqs
Registering database with Oracle Restart
5% complete
[....]
68% complete
Executing Post Configuration Actions
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/primary/primary0.log" for further details.
[oracle@rac2-node1 ~]$ 

[oracle@rac2-node1 ~]$ srvctl status database -d primary -v
Instance primary is running on node rac2-node1. Instance status: Open.
[oracle@rac2-node1 ~]$ 

```

We are also going to add Standby Redo logs.
For Standby Redo Logs, remember to always add 1 extra group than your Redo logs
```bash
SQL> -- REDO Status
col member format a60
col STATUS format a10
set linesize 150
set pagesize 99
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/
  
    GROUP#    THREAD# MEMBER                 ARC STATUS        FSIZE
---------- ---------- ------------------------------------------------------------ --- ---------- ----------
   1      1 /u01/app/oracle/oradata/primary/redo01.log       NO  INACTIVE     50
   2      1 /u01/app/oracle/oradata/primary/redo02.log       NO  INACTIVE     50
   3      1 /u01/app/oracle/oradata/primary/redo03.log       NO  CURRENT      50


SQL> alter database add standby logfile thread 1 group 4 '/u01/app/oracle/oradata/primary/redo04.log' size 50M;
Database altered.

SQL> alter database add standby logfile thread 1 group 5 '/u01/app/oracle/oradata/primary/redo05.log' size 50M;
Database altered.

SQL> alter database add standby logfile thread 1 group 6 '/u01/app/oracle/oradata/primary/redo06.log' size 50M;
Database altered.

SQL> alter database add standby logfile thread 1 group 7 '/u01/app/oracle/oradata/primary/redo07.log' size 50M;
Database altered.

SQL> -- Standby REDO Status
col member format a60
col STATUS format a10
set linesize 150
set pagesize 99
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$standby_log l, v$logfile f
where f.group# = l.group#
order by 1,2
/


    GROUP#    THREAD# MEMBER                 ARC STATUS        FSIZE
---------- ---------- ------------------------------------------------------------ --- ---------- ----------
   4      1 /u01/app/oracle/oradata/primary/redo04.log       YES UNASSIGNED   50
   5      1 /u01/app/oracle/oradata/primary/redo05.log       YES UNASSIGNED   50
   6      1 /u01/app/oracle/oradata/primary/redo06.log       YES UNASSIGNED   50
   7      1 /u01/app/oracle/oradata/primary/redo07.log       YES UNASSIGNED   50
```

We also need to setup the Database in Archive mode
```bash
[oracle@rac2-node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 22 14:52:22 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size        8620080 bytes
Variable Size     687867856 bytes
Database Buffers    503316480 bytes
Redo Buffers        8155136 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL>
```

We are now ready to create the Standby from rac2-node2 
```bash
[oracle@rac2-node2 ~]$ dbca -silent -createDuplicateDB \
> -gdbname primary -sid standby \
> -initParams db_create_file_dest='/u01/app/oracle/oradata/' \
> -primaryDBConnectionString rac2-node1:1521/primary \
> -sysPassword "Welcome1" \
> -createAsStandby \
> -dbUniqueName standby
[WARNING] [DBT-10328] Specified GDB Name (primary) may have a potential conflict with an already existing database on the system.
   ACTION: Specify a different GDB Name that does not conflict with existing databases on the system.
Listener config step
33% complete
Auxiliary instance creation
66% complete
RMAN duplicate
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/standby/primary.log" for further details.
[oracle@rac2-node2 ~]$
```

Since the Clusterware in my cluster already has a Database called primary, it wont add it automatically as it did with the primary system we created earlier, so we have to do it manually.
You should always have primary and standby on different clusters... but this was just a test, of course :)
```bash
[oracle@rac2-node2 ~]$ srvctl add database -d standby \
> -o /u01/app/oracle/product/12.2.0/dbhome_1 \
> -dbtype single \
> -node rac2-node2 \
> -role physical_standby \
> -startoption "read only"
[oracle@rac2-node2 ~]$
```

Add now the primary and standby information to the tnsnames.ora files on both nodes:
```bash
[oracle@rac2-node1 ~]$ nano /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
[....]
PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-node1.raclab.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
  )

LISTENER_PRIMARY =
  (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-node1.raclab.local)(PORT = 1521))


STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-node2.raclab.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )

LISTENER_STANDBY =
  (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-node2.raclab.local)(PORT = 1521))

[oracle@rac2-node1 ~]$ scp -pr /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora rac2-node2:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
tnsnames.ora                                         100% 1801     1.5MB/s   00:00    
[oracle@rac2-node1 ~]$ 

[oracle@rac2-node1 ~]$ tnsping standby
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-node2.raclab.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = standby)))
OK (10 msec)
[oracle@rac2-node1 ~]$ 

[oracle@rac2-node2 ~]$ tnsping primary
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-node1.raclab.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))
OK (0 msec)
[oracle@rac2-node2 ~]$ 
```

Start DG broker on both sides
```bash
[oracle@rac2-node1 ~]$ sqlplus / as sysdba
SQL> ALTER SYSTEM SET dg_broker_start=true;

System altered.

SQL>

[oracle@rac2-node2 ~]$ sqlplus / as sysdba
SQL> ALTER SYSTEM SET dg_broker_start=true;

System altered.

SQL> 
```

and create the DG Broker configuration
```bash
[oracle@rac2-node1 ~]$ dgmgrl /
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sat Aug 22 15:11:29 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "primary"
Connected as SYSDG.
DGMGRL> CREATE CONFIGURATION DG_BROKER_CONFIG AS PRIMARY DATABASE IS primary CONNECT IDENTIFIER IS primary;
Configuration "dg_broker_config" created with primary database "primary"

DGMGRL> ADD DATABASE standby AS CONNECT IDENTIFIER IS standby MAINTAINED AS PHYSICAL;
Database "standby" added

DGMGRL> enable configuration;
Enabled.
```

As a test, we can force a Switch log in primary database and make sure they are trasnported and applied in Standby
```bash

## Primary ##

[oracle@rac2-node1 ~]$ sqlplus / as sysdba
SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.


SQL> select max(sequence#), archived FROM V$LOG where status <>'CURRENT' group by archived;

MAX(SEQUENCE#) ARC
-------------- ---
       9 YES


## Standby ##

[oracle@rac2-node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 22 15:20:14 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select inst_id, process, status MRP_stat, thread#, sequence#, block#, BLOCKS "Total Blocks"
from gv$managed_standby
where process like 'MRP%' or process like 'RFS%' and status != 'IDLE'
order by inst_id,process,thread#; 

   INST_ID PROCESS   MRP_STAT      THREAD#  SEQUENCE#     BLOCK# Total Blocks
---------- --------- ------------ ---------- ---------- ---------- ------------
   1 MRP0      APPLYING_LOG    1       10        170   102400

SQL>  column dummy noprint;
compute sum of Difference on dummy;
break on dummy;

SELECT null dummy, al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied",  almax-lhmax "Difference"
FROM 
 (SELECT thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al,
 (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history  WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh
 WHERE al.thrd = lh.thrd
 ORDER BY 1;

    Thread Last Seq Received Last Seq Applied Difference
---------- ----------------- ---------------- -------------------------
          1                    9                   9                            0
                                                         ------------------------
                                                                                   0


SQL> *    R E C O V E R Y  P R O G R E S S    *

 
 I# Item          SoFar
--- ------------------------- ---------------------------------------------
  1 Last Applied Redo       22-AUG-20 15:20:53 , SCN: 1423907
                                                          ApplyLag +00 00:00:00

  1 Active Apply Rate       37 KB/sec
  1 Average Apply Rate        1 KB/sec
  1 Maximum Apply Rate        37 KB/sec
  1 Redo Applied            0 Megabytes
  1 Log Files             3 Files
  1 Apply Time per Log        3 Seconds
  1 Checkpoint Time per Log   0 Seconds
  1 Active Time             0h 0m 16s
  1 Elapsed Time            0h 6m 24s
                        StartTime: 22-AUG-20 15:14:28
  1 Recovery ID             0 RCVID

11 rows selected.

SQL>
```

This is just an example of what you can do with DBCA. 
Will do new post with 19c version and doing similar thing but for RAC Databases