=========================================================================
Setup for following demonstraton:
Primary:
--Host Name: Linux1
--DB Name: orcl
--Db_Unique_Name: orcl
--Service Name: ORCL
------------------------------------------------------------------------------------------------------------------------
==Standby:
--Host Name: Linux2
--DB Name: orcl
--Db_Unique_Name: orcldr
--Service Name: ORCL_STBY
=========================================================================
Logging:
Switch archivelog mode:
select log_mode from v$database;
alter database archivelog;
Enable force logging:
alter database force logging;
Set FRA for recovery and flashback
SQL> alter system set db_recovery_file_dest_size=10G scope=both sid='*';
SQL> alter system set db_recovery_file_dest='+DATA' scope=both sid='*';
System altered.
Enable Flashback on:
alter database flashback on;
------------------------------------------------------------------------------------------------------------------------
SQL> alter database add standby logfile thread 1 group 10 ('/u01/app/oracle/oradata/ORCL/standby_redo01.log') size 50m;
SQL> alter database add standby logfile thread 1 group 11 ('/u01/app/oracle/oradata/ORCL/standby_redo02.log') size 50m;
SQL> alter database add standby logfile thread 1 group 12 ('/u01/app/oracle/oradata/ORCL/standby_redo03.log') size 50m;
SQL> alter database add standby logfile thread 1 group 13 ('/u01/app/oracle/oradata/ORCL/standby_redo04.log') size 50m;
------------------------------------------------------------------------------------------------------------------------
Initialization Parameters
Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters.
In this case they are both set to "orcl" on the primary database.
Make sure the STANDBY_FILE_MANAGEMENT parameter is set.
alter system set standby_file_management=auto;
------------------------------------------------------------------------------------------------------------------------
Service Setup
Following entries for the primary and standby databases are needed in the tnsnames.ora files on all servers.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = linux1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orcl)
)
)
ORCL_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = linux2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orcl)
)
)
------------------------------------------------------------------------------------------------------------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = orcl)
)
)
Add Following to Standby Listerer.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcldr)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcldr_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = orcl)
)
)
------------------------------------------------------------------------------------------------------------------------
Standby Server Setup
Create a parameter file for the standby database called "/u01/mypfile.ora" with the following contents.
*.db_name='cdb1'
Create a password file, with the SYS password matching that of the primary database.
$ orapwd file=/u01/app/oracle/product/19.0.0/db_1/dbs/orapwcdb1 password=Password1 entries=10
Or
copy from primary using scp
------------------------------------------------------------------------------------------------------------------------
Start the auxiliary instance on the standby server by starting it using the above created pfile "mypfile.ora"
$ export ORACLE_SID=orcl
$ sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='/u01/mypfile.ora';
------------------------------------------------------------------------------------------------------------------------
Connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances.
[oracle@linux1 admin]$ rman target sys/oracle@orcl auxiliary sys/oracle@orcl_stby
Use following rman commands to strat duplication of database from live primary database.
RMAN> duplicate target database
for standby from active database
dorecover
spfile
set db_unique_name='orcl_stby' comment 'Is Standby'
nofilenamecheck;
------------------------------------------------------------------------------------------------------------------------
Enable broker on primary and standby database:
alter system set dg_broker_start=true;
------------------------------------------------------------------------------------------------------------------------
Broker configuration:
[oracle@linux1 admin]$ dgmgrl sys/oracle@orcl
DGMGRL> create configuration dg_config as primary database is orcl connect identifier is orcl;
Configuration "dg_config" created with primary database "orcl"
DGMGRL> add database orcl_stby as connect identifier is orcl_stby maintained as physical;
Database "orcl_stby" added
add database orcl_stby2 as connect identifier is orcl_stby2 maintained as physical;
DGMGRL> enable configuration;
DGMGRL> show configuration;
------------------------------------------------------------------------------------------------------------------------
Stop/Start Managed Recovery
-- Stop managed recovery.
alter database recover managed standby database cancel;
-- Start managed recovery.
alter database recover managed standby database disconnect;
or
alter database recover managed standby database disconnect nodelay;
------------------------------------------------------------------------------------------------------------------------
Database Switchover
Before process switchover or failover, always validate that everything is okay with my DataGuard environment.
DGMGRL> validate database orcl_stby
DGMGRL> switchover to orcl_stby;
$ dgmgrl sys/Password1@cdb1_stby
------------------------------------------------------------------------------------------------------------------------
Database Failover
DGMGRL> failover to orcl_stby;
------------------------------------------------------------------------------------------------------------------------
After using above failover, since the standby database is now the primary database it should be backed up immediately.
The original primary database can now be configured as a standby. If flashback database was enabled on the primary database, then this can be done relatively easily with the following command.
DGMGRL> reinstate database orcl;
------------------------------------------------------------------------------------------------------------------------
Read-Only Standby and Active Data Guard:
Once a standby database is configured, it can be opened in read-only mode to allow query access.
shutdown immediate;
startup mount;
alter database open read only;
alter database recover managed standby database disconnect from session;
------------------------------------------------------------------------------------------------------------------------
Verify Sync:
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;
------------------------------------------------------------------------------------------------------------------------
Check all the above changed parameters:
set linesize 500 pages 0
col value for a90
col name for a50
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_3', 'log_archive_dest_4',
'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');
------------------------------------------------------------------------------------------------------------------------
=========================================================================
