Dear All,
In this post i am going to share you oracle 19c Data guard step by step activity guide .
Primary
database details :
Db name : PROD
Db unique
name :stby
Host
& IP : standby.localdomain
SID : stby
Standby
Database Details :
Db name : PROD
Db unique
name : prod
Host
& IP : primary.localdomain
SID : prod
DG Configuration steps :
Step:1
Enable the archive log mode in the primary database.
alter database archivelog;
archive
log list;
Step:2 Enable the force logging.
SQL>
alter database force logging;
Database
altered.
Note : to
enable flash back we need to setup below pre-requisites
alter system
set db_recovery_file_dest_size= 10G scope=both;
alter system
set db_recovery_file_dest='/u02/app/oracle/fast_recovery_area' scope=both;
Step:3 Flashback should be enabled in primary
database.
SQL>
ALTER DATABASE FLASHBACK ON;
Database
altered.
select name,FORCE_LOGGING,log_mode,flashback_on
from v$database;
Step:4 Add the Standby redo log files for standby
side.
set lines 200
col MEMBER format a65
select
l.GROUP#,l.THREAD#,f.member,l.ARCHIVED,l.status,(bytes/1024/1024)
"size(MB)" from v$log l,v$logfile f where f.group#=l.group# order by
1,2;
select l.GROUP#,l.THREAD#,f.member,l.ARCHIVED,l.status,(bytes/1024/1024)
"size(MB)" from v$standby_log l,v$logfile f where f.group#=l.group#
order by 1,2;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM
V$STANDBY_LOG;
alter database add standby logfile group 4
'/u01/app/oracle/oradata/STBY/redo04.log' size 200m;
alter database add standby logfile group 5 '/u01/app/oracle/oradata/STBY/redo05.log' size 200m;
alter database add standby logfile group 6 '/u01/app/oracle/oradata/STBY/redo06.log' size 200m;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/STBY/redo07.log' size 200m;
select member from v$logfile;
Step:5 Take the backup of Primay database , pfile
and password files on Primary database
create one directory for Primary side backups .
mkdir -p /u01/backup
cd /u01/backup
Create pfile from spfile on Primary database this we can use in the standby database side . there we need to modify some parameter as per the standby db .
create pfile='/u01backup/initprod.ora' from spfile;
copy the primary password file to backup location on Primary node this we need to copy to standby side oracle home / dbs location there we need to rename the sid as per the standby db.
cp /u01/app/oracle/product/19.0.0/db_home/dbs/initstby.ora .
rman target=/ nocatalog <<EOF
run {
sql "alter
system archive log current";
allocate
channel ch1 type disk format '/u01/backup/PROD_bkp_standby_%U';
backup
as compressed backupset database plus archivelog;
backup
current controlfile for standby;
sql
"alter system archive log current";
}
exit;
EOF
Note : Tar the rman backup if you are using manual restore use this backup , if you are using active clone / active rman command no need of this backup .
tar -cvzf rmanbackup.tar.gz rman
tar -xvzf rmanbackup.tar.gz
Step:6 set the log archive config and other
DG parameters on primary database . see the below snap for all the parameters which i set on primary node side .
ALTER SYSTEM SET log_archive_config='dg_config=(prod,stby)'
SCOPE=both;
ALTER SYSTEM SET
log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST
valid_for=(all_logfiles,all_roles) db_unique_name=prod' SCOPE=both;
ALTER SYSTEM SET log_archive_dest_2='service=stby async
valid_for=(online_logfiles,primary_role) db_unique_name=stby' SCOPE=both;
ALTER SYSTEM SET fal_client='prod' SCOPE=both;
ALTER SYSTEM SET fal_server='stby' SCOPE=both;
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
alter system set db_file_name_convert='/u01/app/oracle/oradata/STBY/','/u01/app/oracle/oradata/PROD'
scope=spfile;
alter system set
log_file_name_convert='/u01/app/oracle/oradata/STBY/','/u01/app/oracle/oradata/PROD' scope=spfile;
converts the filename of a
new datafile and logfile respectively on the
primary database to a filename on the
standby database.
crosscheck the DR parameters on primary once
col name for a30
col value for a85
set lines 200 pagesize 200
select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1′,’log_archive_dest_state_2','remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','fal_client','db_file_name_convert','log_file_name_convert','standby_file_management');
Set the log
archive format = ‘%t_%s_%r.arc ,it determines the name of the archive log
file it comes into role when archive log is enabled.
alter system set log_archive_format='%t_%s_%r.arc'
scope=spfile;
Step:7 configure the log_archive_process to 6
,it prevents to avoid any runtime overhead of invoking additional ARCn
process,you can set the LOG_ARCHIVE_MAX_PROCESSES
alter system set log_archive_max_processes=6; ( optional ) default value is 4
show parameter remote_login_passwordfile ( default value )
Step:8 Configure standby database .
alter system set db_unique_name=stby scope=spfile;
select name from v$datafile;
show parameter audit
mkdir -p /u01/app/oracle/oradata/STBY/
mkdir -p /u01/app/oracle/oradata/STBY/pdbseed/
mkdir -p /u01/app/oracle/oradata/STBY/pdb
mkdir -p /u01/app/oracle/oradata/STBY/adump
mkdir -p /u01/app/oracle/fast_recovery_area/
vi
/u01/app/oracle/product/19.0.0/db_home/dbs/initstby.ora
*.audit_file_dest='/u01/app/oracle/admin/stby/adump'
*.audit_trail='db'
*.compatible='19.0.0.0.0'
*.control_files='/u01/app/oracle/oradata/stby/standby1.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_file_name_convert='/u01/app/oracle/oradata/PROD/','/u01/app/oracle/oradata/STBY/'
*.db_name='prod'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/'
*.db_unique_name='stby'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle/admin/stby/adump'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)'
*.enable_pluggable_database=true
*.fal_client='stby'
*.fal_server=''
*.local_listener='LISTENER_PROD'
*.log_archive_config='dg_config=(stby,prod)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stby'
*.log_archive_dest_2='service="prod"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="prod" net_timeout=30','valid_for=(online_logfile,all_roles)'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=5
*.log_file_name_convert='/u01/app/oracle/oradata/PROD/','/u01/app/oracle/oradata/STBY/'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=568m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1704m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
Step:9 Configure network files on both primary
and standby databases.
Listener & TNS entry in the primary database.
[oracle@primary admin]$ cat listener.ora
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = primary.localdomain) ( PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)
(SID_NAME = prod)
)
(SID_DESC =
(GLOBAL_DBNAME = prod_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)
(SID_NAME = prod)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@primary admin]$
[oracle@primary admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/db_home/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)
PDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb)
)
)
LISTENER_PROD =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
[oracle@primary admin]$
Standby side Listener and TNS details :
[oracle@standby admin]$ cat listener.ora
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = standby.localdomain) ( PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stby)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)
(SID_NAME = stby)
)
(SID_DESC =
(GLOBAL_DBNAME = stby_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)
(SID_NAME = stby)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@standby admin]$ cat tnsnames.ora
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
LISTENER_PROD =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
[oracle@standby admin]$
tnsping should work on primary and standby side :
=======================================
Step 10 : update the parameter files on stand by and
start standby database in nomount using newly created pfile.
startup nomount pfile='initstby.ora';
/u01/app/oracle/oradata/TEST,/u01/app/oracle/fast_recovery_area/TEST,/u01/app/oracle/oradata/TEST/pdb1
Fast recovery area location:
/u01/app/oracle/fast_recovery_area/TEST
rman target sys/Welcome_123@prod auxiliary
sys/Welcome_123@stby
duplicate target database for standby from active database nofilenamecheck;
after restore completes enable the mrp process and then stop the db startup , it will start in read only mode .
select max(sequence#) from v$archived_log;
The same information can be obtained by checking the view
v$archived_log, like this:
select sequence# from v$archived_log where registrar = 'RFS'
and applied = 'NO';
select sequence# , first_time,next_time from v$archived_log
order by sequence#;
The actual lag that these non-applied logs causes, can be
found with the query:
set lines 200
col source_db_unique_name format a10
col value format a10
SELECT source_db_unique_name,name,value,unit,time_computed,datum_time FROM v$dataguard_stats WHERE name = 'apply lag';
select name,open_mode,database_role from v$database;
Note : start the mrp
alter database recover managed standby database disconnect nodelay;
stop the mrp :
alter database recover managed standby database cancel;
DG commands :
select name,open_mode,database_role from v$database;
SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
select max(sequence#) from v$archived_log;
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session;
alter database open read only;
alter database archivelog;
alter database open;
select FORCE_LOGGING,log_mode from v$database;
alter system switch logfile;
alter database flashback on;
show parameter recovery;
select group#, thread#, bytes/1024/1024 mb from v$log;
alter database add standby logfile thread 1 size 200m;
select member from v$logfile where type='STANDBY';
alter system set db_unique_name=prod scope=both;
set linesize 500 pages 100
col name format a30
col value format a100
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config',
'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1','log_archive_dest_state_2',
'remote_login_passwordfile', 'log_archive_format', 'log_archive_max_processes',
'fal_server','db_file_name_convert', 'log_file_name_convert', 'standby_file_management');
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope = both;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,stby)' scope = both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=prod' scope = both;
or
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope = both;
ALTER SYSTEM SET log_archive_dest_2='service=stby async valid_for=(online_logfiles,primary_role) db_unique_name=stby' SCOPE=both;
fal_client no longer needed in 19C
alter system set FAL_SERVER='stby' scope = both;
alter system set db_unique_name=prod scope=both;
Thanks,
Srini
