Creating a Physical Standby database from Primary database service

RMAN 'from service' clause enables the restore and recover of primary database files to a standby database through an Oracle Net connection.

Hosts and databases used in this environment.

Sites

Primary

Standby DR

Hosts

srvdb02.localdomain

srvdb03.localdomain

Database Unique Name

orcl

orcldr

Action on Primary database.

1. Enable archive and force logging mode on Primary.

[oracle@srvdb02 ~]$ sqlplus / as sysdba

 SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 16 15:48:06 2023

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> startup mount;

ORACLE instance started.

Total System Global Area  620756648 bytes

Fixed Size                  9137832 bytes

Variable Size             432013312 bytes

Database Buffers          176160768 bytes

Redo Buffers                3444736 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database force logging;

Database altered.

2. Create standby redo logs on the primary database that are the exact same size as the online redo logs.

SQL> select thread#,group#,bytes,status from v$log;

   THREAD#     GROUP#      BYTES STATUS

---------- ---------- ---------- ----------------

         1          1  209715200 INACTIVE

         1          2  209715200 CURRENT

 1                  3  209715200 INACTIVE

SQL> alter database add standby logfile thread 1 group 4 ('/u01/app/oracle/oradata/ORCL/stdbyredo01.log')  size 209715200;

Database altered.

SQL> alter database add standby logfile thread 1 group 5 ('/u01/app/oracle/oradata/ORCL/stdbyredo02.log')  size 209715200;

Database altered.

SQL> alter database add standby logfile thread 1 group 6 ('/u01/app/oracle/oradata/ORCL/stdbyredo03.log')  size 209715200;

Database altered.

SQL> alter database add standby logfile thread 1 group 7 ('/u01/app/oracle/oradata/ORCL/stdbyredo04.log')  size 209715200;

Database altered.

3.Configure dataguard parameters on Primary.

SQL> alter system set log_archive_config='dg_config=(orcl,orcldr)' SCOPE=both;

System altered.

SQL> alter system set log_archive_dest_2='service=orcldr async valid_for=(online_logfiles,primary_role) db_unique_name=orcldr' SCOPE=both;

System altered.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/ORCLDR/','/u01/app/oracle/oradata/ORCL/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/ORCLDR/','/u01/app/oracle/oradata/ORCL/' scope=spfile;

System altered.

SQL> alter system set fal_server=orcldr scope=both;

System altered.

SQL> alter system set fal_client=orcl scope=both;

System altered.

SQL> alter system set standby_file_management=auto scope=both;

System altered.

4.Copy the password file from primary to the standby.

[oracle@srvdb02 ~]$ scp $ORACLE_HOME/dbs/orapworcl oracle@192.168.56.83:$ORACLE_HOME/dbs/orapworcldr

5. Create static listener, add TNS entry for primary and standby DB's.

Primary

Standby DR

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)

      (SID_NAME = orcl)

    )

  )

 

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = srvdb02.localdomain)(PORT = 1521))

  )

 

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcldr)

      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)

      (SID_NAME = orcldr)

    )

  )

 

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = srvdb03.localdomain)(PORT = 1521))

  )

 

ADR_BASE_LISTENER = /u01/app/oracle

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = srvdb02.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

 

ORCLDR =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = srvdb03.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcldr)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = srvdb02.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

 

ORCLDR =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = srvdb03.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcldr)

    )

  )

6. Modify pfile parameters on Standby.

Primary

Standby DR

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/oradata/ORCL/control02.ctl'

*.db_block_size=8192

*.db_file_name_convert='/u01/app/oracle/oradata/ORCLDR/','/u01/app/oracle/oradata/ORCL/'

*.db_name='orcl'

*.db_unique_name='orcl'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.enable_pluggable_database=true

*.fal_client='ORCL'

*.fal_server='ORCLDR'

*.log_archive_config='dg_config=(orcl,orcldr)'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/’

*.log_file_name_convert='/u01/app/oracle/oradata/ORCLDR/','/u01/app/oracle/oradata/ORCL/'

*.memory_target=590m

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.audit_file_dest='/u01/app/oracle/admin/orcldr/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='/u01/app/oracle/oradata/ORCLDR/control01.ctl','/u01/app/oracle/oradata/ORCLDR/control02.ctl'

*.db_block_size=8192

*.db_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/ORCLDR/'

*.db_name='orcl'

*.db_unique_name='orcldr'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldrXDB)'

*.enable_pluggable_database=true

*.fal_client='ORCLDR'

*.fal_server='ORCL'

*.log_archive_config='dg_config=(orcl,orcldr)'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/'

*.log_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/ORCLDR/'

*.memory_target=590m

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

Action on Standby DR database.

1.Create the required directory on the standby side.

mkdir -p /u01/app/oracle/admin/orcldr/adump

mkdir -p /u01/app/oracle/oradata/ORCLDR

mkdir -p /u01/app/oracle/arch

2. Restore standby controlfile using primary database service.

[oracle@srvdb03 ~]$ rman target /

 Recovery Manager: Release 19.0.0.0.0 - Production on Sun Dec 17 01:10:14 2023

Version 19.3.0.0.0

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

connected to target database (not started)

RMAN> startup nomount pfile='/home/oracle/init.ora';

Oracle instance started

Total System Global Area     620756648 bytes

Fixed Size                     9137832 bytes

Variable Size                427819008 bytes

Database Buffers             180355072 bytes

Redo Buffers                   3444736 bytes

 RMAN> restore standby controlfile from service 'ORCL';

Starting restore at 17-DEC-23

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=427 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/ORCLDR/control01.ctl

output file name=/u01/app/oracle/oradata/ORCLDR/control02.ctl

Finished restore at 17-DEC-23 

RMAN> alter database mount;

released channel: ORA_DISK_1

Statement processed

3. Restore standby database from the primary database service and start the MRP apply process. 

RMAN> restore database from service 'ORCL';

Starting restore at 17-DEC-23

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=435 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCLDR/system01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCLDR/users01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/ORCLDR/pdb1/system01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/ORCLDR/pdb1/sysaux01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/ORCLDR/pdb1/undotbs01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service ORCL

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ORCLDR/pdb1/users01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 17-DEC-23

RMAN> sql 'alter database recover managed standby database disconnect from session';

sql statement: alter database recover managed standby database disconnect from session

RMAN> exit

Recovery Manager complete.

4. Check MRP apply process status and create spfile for standby DB. 

[oracle@srvdb03 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 22 22:35:22 2023

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> select thread#,sequence#,process,status,blocks,block# from v$managed_standby;

 

   THREAD#  SEQUENCE# PROCESS   STATUS           BLOCKS     BLOCK#

---------- ---------- --------- ------------ ---------- ----------

         0          0 ARCH      CONNECTED             0          0

         0          0 DGRD      ALLOCATED             0          0

         0          0 DGRD      ALLOCATED             0          0

         0          0 ARCH      CONNECTED             0          0

         0          0 ARCH      CONNECTED             0          0

         0          0 ARCH      CONNECTED             0          0

         1          0 RFS       IDLE                  0          0

         1         17 RFS       IDLE                  1     213781

         1         17 MRP0      APPLYING_LOG     409600     213781

9 rows selected.

SQL> create spfile from pfile='/home/oracle/init.ora';

File created.



Comments

Post a Comment

Popular posts from this blog

Step-by-Step Guide to Configure Oracle GoldenGate Plug-in for Enterprise Manager 13cR5

Image
Oracle GoldenGate Enterprise Manager Plug-in helps monitor and manage Goldengate processes through Oracle Enterprise Manager Cloud Control. Environment details: Host: srvdb01.localdomain Operating System: OEL 8.8 OEM Version: 13.5.0.0 OEM Home: /u01/app/oracle/em13c/oraclehome Database Version: 19.23.0.0.0 Database Home: /u01/app/oracle/product/19.0.0/dbhome_1 Goldengate Version: 21.3.0.0.0 Goldengate Home: /u01/app/oracle/product/21.3.0/ogg_1 Goldengate Agent Home: /u01/app/oracle/oggmon_agent122   All components, including Oracle Enterprise Manager 13.5, Oracle Database 19c, Oracle Goldengate 21.3 and Goldengate Monitor Agent 12.2 are configured on the same host srvdb01.localdomain running on OEL 8.8. We will set up the Oracle GoldenGate Plug-in for Enterprise Manager 13cR5. 1. Download the Oracle Goldengate Plug-in for OEM 13.5 from edelivery.oracle.com. https://www.oracle.com/middleware/technologies/goldengate-downloads.html 2. Copy the Oracle Goldengate Plug-in to the OMS ...
Read more

Step-by-Step Guide to Configure Oracle GoldenGate Veridata 23c

Image
Oracle Goldengate Veridata is a fast tool that compares data between source and target databases, report differences and fixes them. Oracle GoldenGate Veridata 23c supports only MySQL Server as the repository database for storing metadata. The environment consists of: Host: dbsrv01.localdomain : Veridata Agent 23c, Source Database (19c) running on OEL 8.8, extracting  HR.REGIONS table using GoldenGate 21.3 Host: dbsrv02.localdomain : Veridata Agent 23c, Target Database (19c) running on OEL 8.8, replicating HR.REGIONS table using GoldenGate 21.3 Host: dbsrv03.localdomain : Veridata Server 23c, running on OEL 8.8 We will set up the Veridata Server and Veridata Agent 23c for data comparison and synchronization. 1. Download the Oracle Goldengate Veridata installation file from Oracle Software Delivery Cloud. 2.   Copy the Oracle Goldengate Veridata installer to the target server and unzip under /tmp. [oracle@dbsrv03 ~]$ cd /tmp [or...
Read more

How to Reposition Extract, Data Pump and Replicat in GoldenGate During Trail File Corruption

In this blog, we will walk you through the steps to reposition the Extract, Data Pump and Replicat processes in the event of Extract trail file corruption.   The environment consists of: Host: dbsrv01.localdomain: Source Database (19c) named "master" running on OEL 8.8, Extracting HR.REGIONS table using GoldenGate 21.3. Host: dbsrv02.localdomain: Target Database (19c) named "orcl" running on OEL 8.8, Replicating HR.REGIONS table using GoldenGate 21.3. Source (master) Target (orcl) 1. We have an integrated Extract running on the source database.   [oracle@dbsrv01 ~]$ ggsci   Oracle GoldenGate Command Interpreter for Oracle Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047_FBO Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 29 2021 03:59:23 Operating system character set identified as UTF-8.   Copyright (C) 1995, 2021, Oracle and/or its...
Read more