Steps to configure Oracle Data Guard 12.2 for non-multitenant database
This document describes the configuration of the Oracle Data Guard Version 12.2. We will configure a standby for a non-multitenant database.
Our test environment consists of two Linux (OEL 7.3) servers: oradg1 and oradg2. I already created the non-multitenant database on the server oradg1. We will configure the standby database on the server oradg2. We will use a new feature of 12.2 by creating a physical standby via dbca – oracle database creation assistant.
Environment overview:
Primary server name/os: oradg1/Oracle Linux Server 7.3 (64 bit)
Standby server name/os: oradg2/Oracle Linux Server 7.3 (64 bit)
RDBMS Version (primary and standby): 12.2.0.1.0
ORACLE_HOME (primary and standby): /u01/app/oracle/product/12.2.0/dbhome_1
Primary ORACLE_SID/DB_UNIQUE_NAME: ORCL1/ORCL1PRM
Standby ORACLE_SID/DB_UNIQUE_NAME: ORCL1/ORCL1STB
Listener name/port (primary and standby): LISTENER/1521
Path to DB files (primary and standby): /u01/app/oracle/oradata/orcl1
Recovery area (primary and standby): /u01/app/oracle/fast_recovery_area/orcl1Blueprint:
Preparing the primary database
All steps will be executed on the server oradg1 as OS user oracle. Set the environment and start SQLPlus:
[oracle@oradg1 ~]$ . oraenv ORACLE_SID = [orcl1] ? orcl1 [oracle@oradg1 ~]$ sqlplus / as sysdba
Enable the archivelog modus – in my configuration is already done:
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 8 Next log sequence to archive 10 Current log sequence 10
Enable force logging and flashback on the primary:
SQL> alter database force logging; SQL> alter database flashback on;
Create standby redologs
— Check the number of groups and a size of redolog files:
SQL> select GROUP#,THREAD#,BYTES from v$log; GROUP# THREAD# BYTES ---------- ---------- ---------- 1 1 209715200 2 1 209715200 3 1 209715200 SQL> select member from v$logfile; MEMBER ----------------------------------------------------------------------- /u01/app/oracle/oradata/orcl1/redo01.log /u01/app/oracle/oradata/orcl1/redo02.log /u01/app/oracle/oradata/orcl1/redo03.log
— Create:
SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl1/redostb01.log' size 209715200; SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl1/redostb02.log' size 209715200; SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl1/redostb03.log' size 209715200;
Adjust some initialization parameters:
SQL> show parameter db_name NAME TYPE VALUE ---------------------------------------------- db_name string orcl1 SQL> show parameter db_unique_name NAME TYPE VALUE ----------------------------------------------- db_unique_name string ORCL1PRM -- remote_login_password: SQL> show parameter remote_login_password NAME TYPE VALUE ----------------------------------------------- remote_login_passwordfile string EXCLUSIVE -- LOG_ARCHIVE_CONFIG: SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl1prm,orcl1stb)' scope = spfile; -- LOG_ARHIVE_DEST_1: SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl1prm' scope = spfile; -- Parameter for Swith- Failover -These parameters take effect when the primary database is transitioned to the standby role: SQL> alter system set FAL_SERVER='orcl1stb' scope = spfile; SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope = spfile;
Now we will restart the database und check parameters:
SQL> shutdown immediate
SQL> startup
SQL> set linesize 500 pages 100
SQL> col name for a30
SQL> col value forma a100
SQL>
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');
NAME VALUE
--------------------------------------------------------------------------
log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl1prm
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
fal_server orcl1stb
log_archive_config DG_CONFIG=(orcl1prm,orcl1stb)
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name orcl1
db_unique_name ORCL1PRMNetwork configuration
Now we will edit two configuration files: listener.ora and tnsnames.ora:
[oracle@oradg1 dbhome_1]$ cd $ORACLE_HOME/network/admin
Edit file listener.ora, add following lines:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl1prm) (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1) (SID_NAME = orcl1) ) (SID_DESC = (GLOBAL_DBNAME = orcl1prm_dgmgrl) (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1) (SID_NAME = orcl1) ) )
Add following lines to the file tnsnames.ora:
# Data Guard ORCL1PRM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradg1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1prm) (UR=A) ) ) ORCL1STB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradg2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1stb) (UR=A) ) ) LISTENER_ORCL1 = (ADDRESS = (PROTOCOL = TCP)(HOST = oradg1)(PORT = 1521))
Restart the listener:
[oracle@oradg1 admin]$ lsnrctl stop [oracle@oradg1 admin]$ lsnrctl start
Creating a physical standby database
Now we will create the physical standby database on the server oradg2. We will use oracle database creation assistant – dbca – it is a new feature in the RDBMS 12.2.
We will set the environment and start the listener:
[oracle@oradg2 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1 [oracle@oradg2 ~]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@oradg2 ~]$ lsnrctl start
Now we can start the dbca.
The syntax is:
dbca -createDuplicateDB -gdbName global_database_name -primaryDBConnectionString easy_connect_string_to_primary -sid database_system_identifier [-createAsStandby [-dbUniqueName db_unique_name_for_standby]]
In our case we will start the dbca with following arguments:
[oracle@oradg2 ~]$ dbca -silent -createDuplicateDB -gdbName orcl1 -primaryDBConnectionString oradg1:1521/orcl1prm -sid orcl1 -createAsStandby -dbUniqueName orcl1stb
Output:
Enter SYS user password: Listener config step 33% complete Auxiliary instance creation 66% complete RMAN duplicate 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcl1stb/orcl10.log" for further details.
The physical standby database is now created.
The spfile and password file are automatically created in the $ORACLE_HOME/dbs directory:
[oracle@oradg2 ~]$ cd $ORACLE_HOME/dbs [oracle@oradg2 dbs]$ ll *orcl1* -rw-rw----. 1 oracle oinstall 1544 14. Aug 18:42 hc_orcl1.dat -rw-r-----. 1 oracle oinstall 94 14. Aug 18:41 initorcl1.ora -rw-r-----. 1 oracle oinstall 3584 14. Aug 18:41 orapworcl1 -rw-r-----. 1 oracle oinstall 3584 14. Aug 18:42 spfileorcl1.ora
Check the database role on the standby:
[oracle@oradg2 ~]$ . oraenv ORACLE_SID = [oracle] ? orcl1 [oracle@oradg2 ~]$ sqlplus / as sysdba SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY
Configuring a physical standby database
Set database parameters:
[oracle@oradg2 dbs]$ . oraenv ORACLE_SID = [oracle] ? orcl1 [oracle@oradg2 dbs]$ sqlplus / as sysdba SQL> show parameter db_name NAME TYPE VALUE ----------------------------------------------- db_name string orcl1 SQL> show parameter db_unique_name NAME TYPE VALUE ----------------------------------------------- db_unique_name string orcl1stb SQL> -- remote_login_password: SQL> show parameter remote_login_password NAME TYPE VALUE ----------------------------------------------- remote_login_passwordfile string EXCLUSIVE SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl1stb,orcl1prm)' scope = spfile; SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl1stb' scope = spfile; SQL> alter system set FAL_SERVER='orcl1prm' scope = spfile; SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope = spfile;
Shutdown the database and start it in the mount modus:
SQL> shutdown immediate SQL> startup mount
Check parameters:
SQL> set linesize 500 pages 100
SQL> col name for a30
SQL> col value forma a100
SQL>
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');
NAME VALUE
------------------------------ ----------------------------------------------------------------------------------------------------
db_file_name_convert
log_file_name_convert
log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl1stb
log_archive_dest_2
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
fal_server orcl1prm
log_archive_config dg_config=(orcl1stb,orcl1,orcl1prm)
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name orcl1
db_unique_name orcl1stb
Create standby logfiles:
SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl1/redostb01.log' size 209715200;
SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl1/redostb02.log' size 209715200;
SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/orcl1/redostb03.log' size 209715200;Network configuration
We will configure the network configuration for a data guard.
[oracle@oradg2]$ cd $ORACLE_HOME/network/admin
Edit file listener.ora, add following lines:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl1stb) (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1) (SID_NAME = orcl1) ) (SID_DESC = (GLOBAL_DBNAME = orcl1stb_dgmgrl) (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1) (SID_NAME = orcl1) ) )
Add following lines to the file tnsnames.ora:
# Data Guard ORCL1PRM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradg1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1prm) (UR=A) ) ) ORCL1STB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradg2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1stb) (UR=A) ) ) LISTENER_ORCL1 = (ADDRESS = (PROTOCOL = TCP)(HOST = oradg1)(PORT = 1521))
Restart the listener:
[oracle@oradg2 admin]$ lsnrctl stop [oracle@oradg2 admin]$ lsnrctl start
Enabling Data Guard configuration
Enable the data guard broker via SQLPlus (on both servers: oradg1/oradg2):
Server oradg1:
[oracle@oradg1 ~]$ . oraenv ORACLE_SID = [orcl1] ? [oracle@oradg1 ~]$ sqlplus / as sysdba SQL> alter system set dg_broker_start=true scope=both;
Server oradg2:
[oracle@oradg2 ~]$ . oraenv ORACLE_SID = [orcl1] ? [oracle@oradg2]$ sqlplus / as sysdba SQL> alter system set dg_broker_start=true scope=both;
Creating a Data Guard configuration via a tool DGMGRL on the Server oradg1:
[oracle@oradg1 ~]$ . oraenv ORACLE_SID = [orcl1] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@oradg1]$ dgmgrl / DGMGRL> create configuration orcl1 as primary database is orcl1prm connect identifier is orcl1prm; DGMGRL> add database orcl1stb as connect identifier is orcl1stb maintained as physical; DGMGRL> enable configuration;
Check configuration:
DGMGRL> show configuration Configuration - orcl1 Protection Mode: MaxPerformance Members: orcl1prm - Primary database orcl1stb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 55 seconds ago) DGMGRL> show database orcl1prm Database - orcl1prm Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): orcl1 Database Status: SUCCESS DGMGRL> show database orcl1stb Database - orcl1stb Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 11.00 KByte/s Real Time Query: ON Instance(s): orcl1 Database Status: SUCCESS
Set the parameter StaticConnectIdentifier to prevent errors during switchover
DGMGRL> edit database orcl1prm set property StaticConnectIdentifier="oradg1:1521/orcl1prm_dgmgrl"; DGMGRL> edit database orcl1stb set property StaticConnectIdentifier="oradg2:1521/orcl1stb_dgmgrl";
Check Data Guard in SQLPlus (on both servers oradg1/oradg2):
Database role/status:
Primary:
SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE; DATABASE_ROLE INSTANCE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS ---------------- ------------- ----------- -------------------- -------------------- -------------------- PRIMARY ORCL1PRM READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY
Standby:
SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE; DATABASE_ROLE INSTANCE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS ---------------- ---------- --------------------- -------------------- -------------------- -------------------- PHYSICAL STANDBY orcl1stb READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED
Check sequence number of archived redologs:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Output primary:
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
15 14-AUG-17 14-AUG-17
16 14-AUG-17 14-AUG-17
17 14-AUG-17 14-AUG-17
18 14-AUG-17 14-AUG-17
19 14-AUG-17 15-AUG-17
20 15-AUG-17 15-AUG-17
Output standby:
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
17 14-AUG-17 14-AUG-17
18 14-AUG-17 14-AUG-17
19 14-AUG-17 15-AUG-17
20 15-AUG-17 15-AUG-17 Swithover test
Start DGMGRL and connect to Standby database as user sys:
[oracle@oradg1 ~]$ dgmgrl sys/oracle@orcl1stb
Check the configuration:
DGMGRL> show configuration Configuration - orcl1 Protection Mode: MaxPerformance Members: orcl1prm - Primary database orcl1stb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 32 seconds ago)
Perform the swithover:
DGMGRL> switchover to orcl1stb
Output:
Performing switchover NOW, please wait... New primary database "orcl1stb" is opening... Operation requires start up of instance "orcl1" on database "orcl1prm" Starting instance "orcl1"... ORACLE instance started. Database mounted. Connected to "ORCL1PRM" Switchover succeeded, new primary is "orcl1stb"
Check the configuration:
DGMGRL> show configuration Configuration - orcl1 Protection Mode: MaxPerformance Members: orcl1stb - Primary database orcl1prm - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 6 seconds ago)
Comments
Post a Comment