![Cover image for Create Single Instance Standby Using DBCA [12.2 Version]](https://media2.dev.to/dynamic/image/width=1000,height=420,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Fvh99yhqog79zenyimf2j.png)
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:
[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
[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
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
[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
[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 :)
[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:
[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
[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
[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
## 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
Top comments (0)