How to create physical standby database on Oracle 19c


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

No comments:

Post a Comment