OS Version:-Oracle Linux 7
Oracle version:- Oracle Database 19c Enterprise Edition Release 19.3.0 64bit
System Configuration
primary server:-
edit the following files
cat /etc/hosts
[oracle@primary ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.0.110 primary.soumya.com primary
192.168.0.120 standby.soumya.com standby
Standby Server:-
edit the following files
[oracle@standby dbhome_1]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.0.110 primary.soumya.com primary
192.168.0.120 standby.soumya.com standby
In both server– # systemctl stop firewalld
# systemctl stop iptables
We have a database called orclcdb on primary server and we will install only oracle binaries on standby server.
No database should be running in standby server.
Primary database:- orclcdb
Standby database:- orclstd
Primary server side Configuration:-
[oracle@primary ~]$ export ORACLE_SID=orclcdb
[oracle@primary ~]$ sqlplus / as sysdba
SQL> select log_mode from v$database;
LOG_MODE
————
NOARCHIVELOG
Put the database in archivelog mode
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch
Oldest online log sequence 8
Current log sequence 10
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1644164416 bytes
Fixed Size 9135424 bytes
Variable Size 1023410176 bytes
Database Buffers 603979776 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> alter database archivelog;
SQL> alter database open;
Enable force logging The FORCE LOGGING option to ensure that all the changes made in the database
will be captured and available for recovery in the redo logs.
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
—————————————
YES
SQL> ALTER DATABASE FLASHBACK ON;
ALTER DATABASE FLASHBACK ON
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
SQL> alter system set db_recovery_file_dest_size=100g scope=spfile;
SQL> alter system set db_recovery_file_dest=’/u01/archive_log’ scope=spfile;
SQL> shut immediate
SQL> startup
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
Oracle Net configuration:-
Primary Server:-
cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.soumya.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orclcdb_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = orclcdb)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
orclcdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.soumya.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orclcdb)
)
)
orclstd =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.soumya.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orclcdb)
)
)
[oracle@primary ~]$ lsnrctl stop
[oracle@primary ~]$ lsnrctl start
[oracle@primary admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 12-APR-2020 20:39:52
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primary.soumya.com)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 12-APR-2020 20:38:43
Uptime 0 days 0 hr. 1 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/primary/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary.soumya.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “86b637b62fdf7a65e053f706e80a27ca” has 1 instance(s).
Instance “orclcdb”, status READY, has 1 handler(s) for this service…
Service “a25c65a4879321f3e0536e00a8c0826a” has 1 instance(s).
Instance “orclcdb”, status READY, has 1 handler(s) for this service…
Service “orclcdb” has 1 instance(s).
Instance “orclcdb”, status READY, has 1 handler(s) for this service…
Service “orclcdbXDB” has 1 instance(s).
Instance “orclcdb”, status READY, has 1 handler(s) for this service…
Service “orclcdb_DGMGRL” has 1 instance(s).
Instance “orclcdb”, status UNKNOWN, has 1 handler(s) for this service…
Service “orclpdb1” has 1 instance(s).
Instance “orclcdb”, status READY, has 1 handler(s) for this service…
The command completed successfully
Standby Server:-
[oracle@standby ~]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.soumya.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orclstd_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = orclcdb)
)
)
cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
orclcdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.soumya.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orclcdb)
)
)
orclstd =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.soumya.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orclcdb)
)
)
[oracle@standby ~]$ lsnrctl stop
[oracle@standby ~]$ lsnrctl start
[oracle@standby ]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 12-APR-2020 20:52:37
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby.soumya.com)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 12-APR-2020 20:45:57
Uptime 0 days 0 hr. 6 min. 39 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/standby/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.soumya.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “orclstd_DGMGRL” has 1 instance(s).
Instance “orclcdb”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
On primary server:-
SQL> show parameter db_name
NAME TYPE VALUE
———————————— ———– ——————————
db_name string orclcdb
SQL> show parameter db_unique_name
NAME TYPE VALUE
———————————— ———– ——————————
db_unique_name string orclcdb
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Create standby redolog files on primary server:-
SQL> select * from V$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
———- ——- ——- ————————————————– — ———-
3 ONLINE /u01/app/oracle/oradata/ORCLCDB/redo03.log NO 0
2 ONLINE /u01/app/oracle/oradata/ORCLCDB/redo02.log NO 0
1 ONLINE /u01/app/oracle/oradata/ORCLCDB/redo01.log NO 0
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
———- ———- ———- ———- ———- ———- — —————- ————- ——— ———— ——— ———-
1 1 10 209715200 512 1 YES INACTIVE 2426521 08-APR-20 2448164 08-APR-20 0
2 1 11 209715200 512 1 NO CURRENT 2448164 08-APR-20 1.8447E+19 0
3 1 9 209715200 512 1 YES INACTIVE 2312479 06-APR-20 2426521 08-APR-20 0
SQL> select bytes/1024/1024 from v$log;
BYTES/1024/1024
—————
200
200
200
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 (‘/u01/app/oracle/oradata/ORCLCDB/standby_redo01.log’) SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 (‘/u01/app/oracle/oradata/ORCLCDB/standby_redo02.log’) SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 (‘/u01/app/oracle/oradata/ORCLCDB/standby_redo03.log’) SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 (‘/u01/app/oracle/oradata/ORCLCDB/standby_redo04.log’) SIZE 200M;
SQL> select * from V$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
———- ——- ——- ————————————————– — ———-
3 ONLINE /u01/app/oracle/oradata/ORCLCDB/redo03.log NO 0
2 ONLINE /u01/app/oracle/oradata/ORCLCDB/redo02.log NO 0
1 ONLINE /u01/app/oracle/oradata/ORCLCDB/redo01.log NO 0
4 STANDBY /u01/app/oracle/oradata/ORCLCDB/standby_redo01.log NO 0
5 STANDBY /u01/app/oracle/oradata/ORCLCDB/standby_redo02.log NO 0
6 STANDBY /u01/app/oracle/oradata/ORCLCDB/standby_redo03.log NO 0
7 STANDBY /u01/app/oracle/oradata/ORCLCDB/standby_redo04.log NO 0
Create a password file, with the SYS password matching that of the primary database.
[oracle@primary dbs]$ orapwd file=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapworclcdb password=admin123# entries=10
chmod 775 orapworclcdb
scp orapworclcdb oracle@standby:/u01/app/oracle/product/19.0.0/dbhome_1/dbs/
In Standby Server:-
Create a parameter file for the standby database called “/tmp/initorclstd.ora” with the following contents.
*.db_name=’orclcdb’
$ export ORACLE_SID=orclcdb
$ sqlplus / as sysdba
SQL> startup nomount pfile=’/tmp/initorclstd.ora’;
Create the necessary directories on the standby server.
mkdir -p /u01/app/oracle/oradata/orclstd
mkdir -p /u01/app/oracle/oradata/orclstd/pdbseed
mkdir -p /u01/app/oracle/oradata/orclstd/orclpdb1
mkdir -p /u01/app/oracle/admin/orclstd/adump
mkdir -p /u01/app/oracle/admin/orclcdb/adump
mkdir -p /u01/archive_log
Connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances. Do not attempt to use OS
authentication.
Run this from standby database.
[oracle@standby dbs]$ rman TARGET sys/admin123#@orclcdb AUXILIARY sys/admin123#@orclstd
Recovery Manager: Release 19.0.0.0.0 – Production on Sun Apr 12 21:05:44 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLCDB (DBID=2807809769)
connected to auxiliary database: ORCLCDB (not mounted)
Run this from rman.
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET CONTROL_FILES=’/u01/app/oracle/oradata/orclstd/control01.ctl’
SET db_unique_name=’orclstd’ COMMENT ‘Is standby’
set db_file_name_convert=’/u01/app/oracle/oradata/ORCLCDB/’,’/u01/app/oracle/oradata/orclstd/’
set log_file_name_convert=’/u01/app/oracle/oradata/ORCLCDB/’,’/u01/app/oracle/oradata/orclstd/’
SET job_queue_processes=’0′
NOFILENAMECHECK;
}
Enable Broker
Now Enable Data guard broker in both database. Run this in both database
SQL>ALTER SYSTEM SET dg_broker_start=true;
In primary DATABASE:-
oracle@primary flashback]$ dgmgrl sys/admin123#@orclcdb
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Mon Apr 13 19:05:31 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected to “orclcdb”
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS orclcdb CONNECT IDENTIFIER IS orclcdb;
DGMGRL>ADD DATABASE orclstd AS CONNECT IDENTIFIER IS orclstd MAINTAINED AS PHYSICAL;
We Faced , an error like below while adding standby database.
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
Solution for above error is given Here. Once we fixed the error,
Enable the configuration.
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> show configuration;
Configuration – my_dg_config
Protection Mode: MaxPerformance
Members:
orclcdb – Primary database
orclstd – Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 60 seconds ago)
DGMGRL> show configuration verbose;
Configuration – my_dg_config
Protection Mode: MaxPerformance
Members:
orclcdb – Primary database
orclstd – Physical standby database
Properties:
FastStartFailoverThreshold = ’30’
OperationTimeout = ’30’
TraceLevel = ‘USER’
FastStartFailoverLagLimit = ’30’
CommunicationTimeout = ‘180’
ObserverReconnect = ‘0’
FastStartFailoverAutoReinstate = ‘TRUE’
FastStartFailoverPmyShutdown = ‘TRUE’
BystandersFollowRoleChange = ‘ALL’
ObserverOverride = ‘FALSE’
ExternalDestination1 = ”
ExternalDestination2 = ”
PrimaryLostWriteAction = ‘CONTINUE’
ConfigurationWideServiceName = ‘orclcdb_CFG’
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
Leave a Reply