Oracle 19c Data Guard Physical Standby Step by Step configuration details.

-

 

Description:-

In this is the article we are going to see step-by-step to create a physical standby  database using RMAN in Oracle Database 19c.

Parameters needs to configure both side for Dataguard,
PRIMARY :
[oracle@trichy ~]$ . oraenv

ORACLE_SID = [cdb1] ? prod

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@trichy ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 15 09:14:12 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup

ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size      8899384 bytes
Variable Size    520093696 bytes
Database Buffers  1879048192 bytes
Redo Buffers      7876608 bytes
Database mounted.
Database opened.
Make sure primary is in archivelog mode
SQL> archive log list

Database log mode             Archive Mode
Automatic archival            Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6

Primary side:
Check FORCE LOGGING is enabled,
SQL> select force_logging from v$database;

FORCE_LOGGING
----------------
NO

SQL> alter database force logging;
Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
----------------
YES

SQL> alter system set log_archive_config='dg_config=(PROD,STANDBY)';
System altered.

SQL> alter system set log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,primary_role)';
System altered.

SQL> alter system set log_archive_dest_2='service=STANDBY lgwr async noaffirm valid_for=(all_logfiles,primary_role) db_unique_name=STANDBY';
System altered.

SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> alter system set log_archive_dest_state_1=enable;
System altered.

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
System altered.

SQL> alter system set standby_file_management=auto;
System altered.

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
System altered.

SQL> alter system set log_archive_max_processes=10;
System altered.

SQL> alter system set db_unique_name=PROD scope=spfile;
System altered.

SQL> alter system set fal_client=PROD;
System altered.

SQL> alter system set fal_server=STANDBY;
System altered.

SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

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

File created.

*.db_unique_name='STANDBY'
*.fal_client='STANDBY'
*.fal_server='PROD'
*.log_archive_config='dg_config=(PROD,STANDBY)'
*.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,primary_role)'
*.log_archive_dest_2='service=PROD lgwr async noaffirm valid_for=(all_logfiles,primary_role) db_unique_name=PROD'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=10
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'

[oracle@trichy ~]$ scp -r initprod.ora oracle@192.168.1.14:/u01/app/oracle/product/19.0.0/dbhome_1/dbs

The authenticity of host '192.168.1.14 (192.168.1.14)' can't be established.

ECDSA key fingerprint is SHA256:jU17jN8XF6AHRAi6HsDXnoQdDYXOlScWvmZWxXK+kw8.

ECDSA key fingerprint is MD5:e9:ac:84:59:86:d4:47:ab:e5:ac:89:23:b1:1c:1e:8c.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.1.14' (ECDSA) to the list of known hosts.

oracle@192.168.1.14's password:

initprod.ora                                                                                 100% 1566     1.1MB/s   00:00

[oracle@trichy ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs

[oracle@trichy dbs]$ ls

hc_prod.dat  init.ora  lkPROD  orapwprod  spfileprod.ora
Copy password file from primary to standby server,
[oracle@trichy dbs]$ scp -r orapwprod oracle@192.168.1.14:/u01/app/oracle/product/19.0.0/dbhome_1/dbs

oracle@192.168.1.14's password:

orapwprod
Listener and TNS Configuration :-
LISTENER_CONFIG
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = prod)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.15 )(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
TNS_CONFIG
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.15)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))

)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
Make the respective directories for standby database,
STANDBY :
[oracle@trichy ~]$ mkdir -p /u01/app/oracle/admin/prod/adump

[oracle@trichy ~]$ mkdir -p /u01/app/oracle/oradata/PROD/

[oracle@trichy ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/PROD/

Edit the parameters in pfile for standby
*.db_unique_name='STANDBY'
*.fal_client='STANDBY'
*.fal_server='PROD'
*.log_archive_config='dg_config=(PROD,STANDBY)'
*.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,primary_role)'
*.log_archive_dest_2='service=PROD lgwr async noaffirm valid_for=(all_logfiles,primary_role) db_unique_name=PROD'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=10
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
Listener and TNS Configuration :-
STANDBY_LISTENER_CONFIG
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = prod)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.14 )(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
STANDBY_TNS_CONFIG
PROD =
DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.15)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
Keep the database in NOMOUNT stage to create standby database,
[oracle@trichy ~]$ . oraenv

ORACLE_SID = [cdb1] ? prod

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@trichy ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 15 09:14:12 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size     8899384 bytes
Variable Size   520093696 bytes
Database Buffers  1879048192 bytes
Redo Buffers     7876608 bytes

SQL>
In standby side,check RMAN connection.Connect primary database as TARGET and standby database as AUXILIARY(creating new instance)   

[oracle@trichy dbs]$ rman target sys/oracle@PROD

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jun 15 10:42:32 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PROD (DBID=422602599)

RMAN> connect auxiliary sys/oracle@STANDBY

connected to auxiliary database: PROD (not mounted) 

RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 15-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=36 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format  '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwprod'   ;
}
executing Memory Script
Starting backup at 15-JUN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
Finished backup at 15-JUN-19
duplicating Online logs to Oracle Managed File (OMF) location
contents of Memory Script:
{
restore clone from service  'PROD' standby controlfile;
}
executing Memory Script
Starting restore at 15-JUN-19
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PROD
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
output file name=/u01/app/oracle/oradata/PROD/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD/control02.ctl
Finished restore at 15-JUN-19
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile  1 to
"/u01/app/oracle/oradata/PROD/temp01.dbf";
switch clone tempfile all;
set newname for datafile  1 to
"/u01/app/oracle/oradata/PROD/system01.dbf";
set newname for datafile  3 to
"/u01/app/oracle/oradata/PROD/sysaux01.dbf";
set newname for datafile  4 to
"/u01/app/oracle/oradata/PROD/undotbs01.dbf";
set newname for datafile  7 to
"/u01/app/oracle/oradata/PROD/users01.dbf";
restore
from  nonsparse   from service
'PROD'   clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/PROD/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 15-JUN-19
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:08:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:11:23
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:59
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 15-JUN-19
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service  'PROD'
archivelog from scn  2187262;
switch clone datafile all;
}
executing Memory Script
Starting restore at 15-JUN-19
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service PROD
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service PROD
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-JUN-19
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1011006513 file name=/u01/app/oracle/oradata/PROD/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1011006513 file name=/u01/app/oracle/oradata/PROD/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1011006513 file name=/u01/app/oracle/oradata/PROD/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1011006513 file name=/u01/app/oracle/oradata/PROD/users01.dbf
contents of Memory Script:
{
set until scn  2190007;
recover standby
clone database
delete archivelog
;
}

executing Memory Script
executing command: SET until clause
Starting recover at 15-JUN-19
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_9_1010764267.arc
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1010764267.arc
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_9_1010764267.arc thread=1 sequence=9
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1010764267.arc thread=1 sequence=10
media recovery complete, elapsed time: 00:00:04
Finished recover at 15-JUN-19
contents of Memory Script:
{
delete clone force archivelog all;
}
executing Memory Script
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_9_1010764267.arc RECID=1 STAMP=1011006509
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1010764267.arc RECID=2 STAMP=1011006510
Deleted 2 objects
Finished Duplicate Db at 15-JUN-19.


Post check status for Standby database,

1.   SQL> select name,database_role,open_mode from v$database;
2.    
3.   NAME DATABASE_ROLE OPEN_MODE
4.   ——— ————-------- ——————–
5.   PROD PHYSICAL STANDBY MOUNTED
6.    
7.    
8.   Enable the recovery:
9.    
10.  SQL> alter database recover managed standby database disconnect from session;
11.   
12.  Database altered.
13.   
14.   
15.  Check the Standby database sync status with primary:
16.   
17.  SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
18.   
19.  Thread Last Sequence Received Last Sequence Applied Difference
20.  ———— ———————————— ———–------------------- ---------------
21.  1 10 10 0

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more