Blog
Oracle 19C Dataguard installation using Active Duplicate Method
- December 30, 2019
- Posted by: Ankush Thavali
- Category: Oracle DBA
In this blog , we will learn about the oracle 12c dataguard installation using active duplicate method.
We are using oracle enterprise linux as database server
Primary | Standby | |
Hostname | prim.oracle.com | stand.oracle.com |
IP | 192.168.0.60 | 192.168.0.61 |
DB Unique Name | PRIM | STAND |
We are assuming that primary database is ready with database installation and we have copied the vmware files to create standby database.
- Change the IP address of standby machine using neat command.
- Edit the hosts file for both primary and standby server. Add the ip ,hostname entry in hosts file.
Primary Machine hosts file will be like this
[oracle@prim ~]$ cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 prim.oracle.com prim localhost.localdomain localhost ::1 localhost6.localdomain6 localhost6 192.168.0.60 prim.oracle.com prim 192.168.0.61 stand.oracle.com stand
Standby Machine hosts file will be like this.
[oracle@stand ~]$ cat /etc/hosts Do not remove the following line, or various programs that require network functionality will fail. 127.0.0.1 stand.oracle.com prim localhost.localdomain localhost ::1 localhost6.localdomain6 localhost6 192.168.0.60 prim.oracle.com prim 192.168.0.61 stand.oracle.com stand
Disable the firewall of both primary and standby machine.
[root@prim ~]# systemctl stop firewalld[root@prim ~]#
systemctl disable firewalld
Primary Side Configuration
Put the database in archive log mode.
SQL> SELECT log_mode FROM v$database; LOG_MODE ------------ NOARCHIVELOG SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN;
Enable force logging
SQL> alter database force logging; At least one log file available SQL> alter system switch logfile;
Check the size of online logfile and create same size standby logfile
select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,
MEMBERS,STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ----------------
1 1 4 200 1 INACTIVE
2 1 5 200 1 ACTIVE
3 1 6 200 1 CURRENT
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/data/app/oracle/oradata/PRIM/redo03.log
/data/app/oracle/oradata/PRIM/redo02.log
/data/app/oracle/oradata/PRIM/redo01.log
Check the size of Online Redo log file
SQL> select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024, MEMBERS,STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ----------------
1 1 10 200 2 INACTIVE
2 1 11 200 2 INACTIVE
3 1 12 200 2 CURRENT
create standby redo logs for switchovers and should be adding one extra.
alter database add standby logfile '/data/app/oracle/oradata/cdb/redo04.log' size 50m; alter database add standby logfile '/data/app/oracle/oradata/cdb/redo05.log' size 50m; alter database add standby logfile '/data/app/oracle/oradata/cdb/redo06.log' size 50m; alter database add standby logfile '/data/app/oracle/oradata/cdb/redo07.log' size 50m;
SELECT group#, type, member FROM v$logfile WHERE type = 'STANDBY' order by group#;
Check DB_NAME & DB_UNIQUE_NAME
SQL> show parameter db_name NAME TYPE VALUE NAME ----------- ----------- ----------- db_name string prim SQL> show parameter db_unique_name TYPE VALUE NAME --------------- ------------ ----------- db_unique_name string prim
set remote archivelog destination for standby & local is in flash_recovery_area.
SQL> alter system set log_archive_dest_2= 'service=stand async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=stand';
The STANDBY_FILE_MANAGEMENT parameter must be set AUTO.
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Listener configuration on primary and standby
Add the entry for both the unique id.
[oracle@prim ~]$ cat /data/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora listener.ora Network Configuration File: /data/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = cdb) (ORACLE_HOME = /data/app/oracle/product/12.1.0.2/db_1) (SID_NAME = cdb) ) (SID_DESC = (GLOBAL_DBNAME = stand) (ORACLE_HOME = /data/app/oracle/product/12.1.0.2/db_1) (SID_NAME = stand) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521)) ) )
TNSNAMES.ORA file configuration for both the server.
[oracle@prim ~]$ cat /data/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora tnsnames.ora Network Configuration File: /data/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora Generated by Oracle configuration tools. LISTENER_CDB = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521)) PDBCDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdbcdb) ) ) STAND = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stand.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stand) ) ) PRIM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prim) ) ) CDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb) ) )
Stop and Start the listener
lsnrctl stop lsnrctl start
Crosscheck with tnsping for both stand and primary database.
[oracle@prim ~]$ tnsping stand TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-DEC-2019 23:48:54 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /data/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stand.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stand))) OK (0 msec) [oracle@prim ~]$ [oracle@prim ~]$ [oracle@prim ~]$ tnsping prim TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-DEC-2019 23:49:05 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /data/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prim))) OK (100 msec)
Set the log_archive_config parameter
SQL> alter system set log_archive_config='dg_config=(prim,stand)';
set remote_login_passwordfile exclusive.
SQL> alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
Update the fal_server and fal_client
SQL> alter system set fal_server='stand';
SQL> alter system set fal_client='prim';
Create pfile from spfile for the standby database
SQL> create pfile from spfile
Move the PFILE,Password file,listener.ora,tnsnames.ora file to standby. Change the name of files as per standby database.
scp orapwcdb oracle@192.168.0.61:$ORACLE_HOME/dbs/orapwstand
scp /data/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora oracle@192.168.0.61: /data/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
scp /data/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora oracle@192.168.0.61: /data/app/oracle/product/12.1.0.2/db_1/network/admin/ listener.ora
Standby Configuration
Create directory on standby for CDB and PDB datafile also.
mkdir -p /data/app/oracle/oradata/STAND/pdbprim
mkdir -p /data/app/oracle/oradata/STAND/PDBPRIM2
mkdir -p /data/app/oracle/oradata/STAND/pdbseed
mkdir -p /data/app/oracle/admin/stand/adump
mkdir -p /data/app/oracle/fast_recovery_area/stand/
Do changes in standby pfile and add following two parameter as log_file_name_convert and db_file_name_convert
PFILE on standby should be look like this.
stand.__data_transfer_cache_size=0
stand.__db_cache_size=285212672
stand.__java_pool_size=4194304
stand.__large_pool_size=8388608
stand.__oracle_base='/data/app/oracle'#ORACLE_BASE set from environment
stand.__pga_aggregate_target=268435456
stand.__sga_target=499122176
stand.__shared_io_pool_size=16777216
stand.__shared_pool_size=176160768
stand.__streams_pool_size=0
*.audit_file_dest='/data/app/oracle/admin/stand/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/data/app/oracle/oradata/STAND/control01.ctl','/data/app/oracle/oradata/STAND/control02.ctl','/u01/app/oracle/oradata/STAND/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prim'
*.db_unique_name='stand'
*.db_recovery_file_dest='/data/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4800m
*.fal_client='stand'
*.fal_server='prim'
*.diagnostic_dest='/data/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.db_file_name_convert='/data/app/oracle/oradata/PRIM','/data/app/oracle/oradata/STAND'
*.log_file_name_convert='/data/app/oracle/oradata/PRIM/','/data/app/oracle/oradata/STAND/'
*.log_archive_config='dg_config=(prim,stand)'
*.log_archive_dest_1='location=/data/archive valid_for=(all_logfiles,all_roles) db_unique_name=stand'
*.log_archive_dest_2='service=prim valid_for=(all_logfiles,primary_role) db_unique_name=prim'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
enable_pluggable_database=true
Start the database in nomount stage using pfile
SQL > startup nomount pfile='initstand.ora'
connect with rman with target & auxiliary instance using the following command.
rman target sys/Oracle123@cdb auxiliary sys/Oracle123@stand
Run the following duplicate command, that command will start copying all database on the standby server.
Duplicate target database for standby from active database dorecover nofilenamecheck;
Dataguard important Queries
Check the database status on primary and standby
select status,instance_name,database_role,protection_mode from v$database,v$instance;
Start the MRP process on standby database
alter database recover managed standby database disconnect from session;
Status of the MRP process
select process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby;
Check if both the primary and standby is in sync
SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
#dataguard #oracle #oracledataguard #ankushthavali
In this blog , we will learn about the oracle 12c dataguard installation using active duplicate method.
We are using oracle enterprise linux as database server
Primary | Standby | |
Hostname | prim.oracle.com | stand.oracle.com |
IP | 192.168.0.60 | 192.168.0.61 |
DB Unique Name | PRIM | STAND |
We are assuming that primary database is ready with database installation and we have copied the vmware files to create standby database.
- Change the IP address of standby machine using neat command.
- Edit the hosts file for both primary and standby server. Add the ip ,hostname entry in hosts file.
Primary Machine hosts file will be like this
[oracle@prim ~]$ cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 prim.oracle.com prim localhost.localdomain localhost ::1 localhost6.localdomain6 localhost6 192.168.0.60 prim.oracle.com prim 192.168.0.61 stand.oracle.com stand
Standby Machine hosts file will be like this.
[oracle@stand ~]$ cat /etc/hosts Do not remove the following line, or various programs that require network functionality will fail. 127.0.0.1 stand.oracle.com prim localhost.localdomain localhost ::1 localhost6.localdomain6 localhost6 192.168.0.60 prim.oracle.com prim 192.168.0.61 stand.oracle.com stand
Disable the firewall of both primary and standby machine.
[root@prim ~]# systemctl stop firewalld[root@prim ~]#
systemctl disable firewalld
Primary Side Configuration
Put the database in archive log mode.
SQL> SELECT log_mode FROM v$database; LOG_MODE ------------ NOARCHIVELOG SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN;
Enable force logging
SQL> alter database force logging; At least one log file available SQL> alter system switch logfile;
Check the size of online logfile and create same size standby logfile
select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,
MEMBERS,STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ----------------
1 1 4 200 1 INACTIVE
2 1 5 200 1 ACTIVE
3 1 6 200 1 CURRENT
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/data/app/oracle/oradata/PRIM/redo03.log
/data/app/oracle/oradata/PRIM/redo02.log
/data/app/oracle/oradata/PRIM/redo01.log
Check the size of Online Redo log file
SQL> select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024, MEMBERS,STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ----------------
1 1 10 200 2 INACTIVE
2 1 11 200 2 INACTIVE
3 1 12 200 2 CURRENT
create standby redo logs for switchovers and should be adding one extra.
alter database add standby logfile '/data/app/oracle/oradata/cdb/redo04.log' size 50m; alter database add standby logfile '/data/app/oracle/oradata/cdb/redo05.log' size 50m; alter database add standby logfile '/data/app/oracle/oradata/cdb/redo06.log' size 50m; alter database add standby logfile '/data/app/oracle/oradata/cdb/redo07.log' size 50m;
SELECT group#, type, member FROM v$logfile WHERE type = 'STANDBY' order by group#;
Check DB_NAME & DB_UNIQUE_NAME
SQL> show parameter db_name NAME TYPE VALUE NAME ----------- ----------- ----------- db_name string prim SQL> show parameter db_unique_name TYPE VALUE NAME --------------- ------------ ----------- db_unique_name string prim
set remote archivelog destination for standby & local is in flash_recovery_area.
SQL> alter system set log_archive_dest_2= 'service=stand async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=stand';
The STANDBY_FILE_MANAGEMENT parameter must be set AUTO.
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Listener configuration on primary and standby
Add the entry for both the unique id.
[oracle@prim ~]$ cat /data/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora listener.ora Network Configuration File: /data/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = cdb) (ORACLE_HOME = /data/app/oracle/product/12.1.0.2/db_1) (SID_NAME = cdb) ) (SID_DESC = (GLOBAL_DBNAME = stand) (ORACLE_HOME = /data/app/oracle/product/12.1.0.2/db_1) (SID_NAME = stand) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521)) ) )
TNSNAMES.ORA file configuration for both the server.
[oracle@prim ~]$ cat /data/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora tnsnames.ora Network Configuration File: /data/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora Generated by Oracle configuration tools. LISTENER_CDB = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521)) PDBCDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdbcdb) ) ) STAND = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stand.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stand) ) ) PRIM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prim) ) ) CDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb) ) )
Stop and Start the listener
lsnrctl stop lsnrctl start
Crosscheck with tnsping for both stand and primary database.
[oracle@prim ~]$ tnsping stand TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-DEC-2019 23:48:54 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /data/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stand.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stand))) OK (0 msec) [oracle@prim ~]$ [oracle@prim ~]$ [oracle@prim ~]$ tnsping prim TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-DEC-2019 23:49:05 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /data/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prim))) OK (100 msec)
Set the log_archive_config parameter
SQL> alter system set log_archive_config='dg_config=(prim,stand)';
set remote_login_passwordfile exclusive.
SQL> alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
Update the fal_server and fal_client
SQL> alter system set fal_server='stand';
SQL> alter system set fal_client='prim';
Create pfile from spfile for the standby database
SQL> create pfile from spfile
Move the PFILE,Password file,listener.ora,tnsnames.ora file to standby. Change the name of files as per standby database.
scp orapwcdb oracle@192.168.0.61:$ORACLE_HOME/dbs/orapwstand
scp /data/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora oracle@192.168.0.61: /data/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
scp /data/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora oracle@192.168.0.61: /data/app/oracle/product/12.1.0.2/db_1/network/admin/ listener.ora
Standby Configuration
Create directory on standby for CDB and PDB datafile also.
mkdir -p /data/app/oracle/oradata/STAND/pdbprim
mkdir -p /data/app/oracle/oradata/STAND/PDBPRIM2
mkdir -p /data/app/oracle/oradata/STAND/pdbseed
mkdir -p /data/app/oracle/admin/stand/adump
mkdir -p /data/app/oracle/fast_recovery_area/stand/
Do changes in standby pfile and add following two parameter as log_file_name_convert and db_file_name_convert
PFILE on standby should be look like this.
stand.__data_transfer_cache_size=0
stand.__db_cache_size=285212672
stand.__java_pool_size=4194304
stand.__large_pool_size=8388608
stand.__oracle_base='/data/app/oracle'#ORACLE_BASE set from environment
stand.__pga_aggregate_target=268435456
stand.__sga_target=499122176
stand.__shared_io_pool_size=16777216
stand.__shared_pool_size=176160768
stand.__streams_pool_size=0
*.audit_file_dest='/data/app/oracle/admin/stand/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/data/app/oracle/oradata/STAND/control01.ctl','/data/app/oracle/oradata/STAND/control02.ctl','/u01/app/oracle/oradata/STAND/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prim'
*.db_unique_name='stand'
*.db_recovery_file_dest='/data/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4800m
*.fal_client='stand'
*.fal_server='prim'
*.diagnostic_dest='/data/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.db_file_name_convert='/data/app/oracle/oradata/PRIM','/data/app/oracle/oradata/STAND'
*.log_file_name_convert='/data/app/oracle/oradata/PRIM/','/data/app/oracle/oradata/STAND/'
*.log_archive_config='dg_config=(prim,stand)'
*.log_archive_dest_1='location=/data/archive valid_for=(all_logfiles,all_roles) db_unique_name=stand'
*.log_archive_dest_2='service=prim valid_for=(all_logfiles,primary_role) db_unique_name=prim'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
enable_pluggable_database=true
Start the database in nomount stage using pfile
SQL > startup nomount pfile='initstand.ora'
connect with rman with target & auxiliary instance using the following command.
rman target sys/Oracle123@cdb auxiliary sys/Oracle123@stand
Run the following duplicate command, that command will start copying all database on the standby server.
Duplicate target database for standby from active database dorecover nofilenamecheck;
Dataguard important Queries
Check the database status on primary and standby
select status,instance_name,database_role,protection_mode from v$database,v$instance;
Start the MRP process on standby database
alter database recover managed standby database disconnect from session;
Status of the MRP process
select process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby;
Check if both the primary and standby is in sync
SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
#dataguard #oracle #oracledataguard #ankushthavali