OS: Oracle Linux 7.3
DB: 12.2.0
Step 0: For Linux OS do followings on each machine
Stop and disable firewall as
[oracle@db admin]$systemctl stop firewalld
[oracle@db admin]$systemctl disable firewalld
Disable selinux as
[root@db admin]$vi /etc/selinux/config
SELINUX=disabled
SSH connection must be established between both databases as
ssh-keygen
ssh-copy-id db_hostname
------> Primary Database <------
Step 1: Enable Forced Logging
As part of preparing the primary database for standby database creation, you place the primary database in FORCE LOGGING mode.
To check the database logging mode:
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------
NO
You can do this after database creation using the following SQL statement:
SQL> ALTER DATABASE FORCE LOGGING;
Note: When you issue this statement, the primary database must at least be mounted (and it can also be open).
Step 2: Configure Redo Transport Authentication
Oracle Data Guard uses Oracle Net sessions to transport redo data and control
messages between the members of an Oracle Data Guard configuration.
These redo transport sessions are authenticated using either the Secure Sockets Layer
(SSL) protocol or a remote login password file.
Step 3: Set Primary Database Initialization Parameters
On the primary database, you define initialization parameters that control redo
transport services while the database is in the primary role.
There are additional parameters you need to add that control the receipt of the redo
data and apply services when the primary database is transitioned to the standby role.
The following example shows the primary role initialization parameters that you
maintain on the primary database. This example represents an Oracle Data Guard
configuration with a primary database located in Chicago (orcl) and one physical standby
database located in Boston (stby). The parameters shown in this example are valid for the
Chicago database when it is running in either the primary or the standby database role.
Content of initorcl.ora file. (create pfile from spfile, change pfile, start database with pfile, create spfile frpm pfile, force startup)
Note: We have already enabled the archive log here and db_recover_file_* parameters are defined. So we will use 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
in parameter file.
# The lines start without '*' is new added.
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
db_unique_name=orcl
*.db_recovery_file_dest='/u01/app/oracle/recovery_area'
*.db_recovery_file_dest_size=8016m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.local_listener='LISTENER_ORCL'
*.memory_target=1570m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
log_archive_config='DG_CONFIG=(orcl,stby)'
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
log_archive_dest_2='SERVICE=stby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby'
log_archive_format=%t_%s_%r.arc
# These parameters take effect when the primary database is transitioned to the standby role
FAL_SERVER=stby
FAL_CLIENT=orcl
DB_FILE_NAME_CONVERT='/stby/','/orcl/'
LOG_FILE_NAME_CONVERT='/stby/','/orcl/'
STANDBY_FILE_MANAGEMENT=AUTO
Option 2: We can also update parameters from sqlplus.
SQL> alter system set db_unique_name=orcl scope=spfile;
SQL> alter system set log_archive_config='DG_CONFIG=(orcl,stby)' scope=both;
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both;
SQL> alter system set log_archive_dest_2='SERVICE=stby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby' scope=both;
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> alter system set FAL_SERVER=stby scope=both;
SQL> alter system set FAL_CLIENT=orcl scope=both;
SQL> alter system set DB_FILE_NAME_CONVERT='/stby/','/orcl/' scope=spfile;
SQL> alter system set LOG_FILE_NAME_CONVERT='/stby/','/orcl/' scope=spfile;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
SQL> alter system set remote_login_passwordfile=exclusive; -->REMOTE LOGIN (default is 'exclusive')
SQL> startup force;
Note: For explanation of each line refer to page 57. (documentation "Data Guard Concepts and Administration").
Another useful link for standby creation: http://onlineappsdba.com/index.php/2009/08/19/standby-redo-logs-srl-in-standby-database/
Step 4: Enable Archiving
If archiving is not enabled, then you must put the primary database in ARCHIVELOG mode and enable automatic archiving.
Issue the following SQL statements:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
Step 5: Create a Backup Copy of the Primary Database Data Files
[oracle@db admin]$ rman target=/
RMAN> backup database plus archivelog tag='for_stby';
Starting backup at 01-DEC-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 01-DEC-17
channel ORA_DISK_1: finished piece 1 at 01-DEC-17
piece handle=/u01/app/oracle/recovery_area/ORCL/backupset/2017_12_01/o1_mf_nnndf_FOR_STBY_f21wgkrc_.bkp tag=FOR_STBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 01-DEC-17
Starting Control File and SPFILE Autobackup at 01-DEC-17
piece handle=/u01/app/oracle/recovery_area/ORCL/autobackup/2017_12_01/o1_mf_s_961582112_f21wh125_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 01-DEC-17
RMAN>
Step 6: Configure the Primary Database to Receive Redo Data
It is a best practice to configure the primary database to receive redo if this is the first
time a standby database is added to the configuration.
The primary database can then quickly transition to the standby role and begin receiving redo data, if necessary.
To create a standby redo log, use the SQL ALTER DATABASE ADD STANDBY LOGFILE statement. For example:
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog1.rdo') SIZE 200M;
or
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
We added 4 standby logfile in our test environment.
Note: Each standby redo log file must be at least as large as the largest redo log file in the
redo log of the redo source database. For administrative ease, Oracle recommends that
all redo log files in the redo log at the redo source database and the standby redo log
at a redo transport destination be of the same size.
The standby redo log must have at least one more redo log group than the redo log at
the redo source database, for each redo thread at the redo source database. At the redo
source database, query the V$LOG view to determine how many redo log groups are in
the redo log at the redo source database and query the V$THREAD view to determine
how many redo threads exist at the redo source database.
Step 7: Create a Control File for the Standby Database
Create the control file for the standby database, as shown in the following example
(the primary database does not have to be open, but it must at least be mounted):
SQL> alter database create standby controlfile as '/tmp/stby.ctl';
Database altered.
The ALTER DATABASE command designates the database that is to operate in the
standby role; in this case, a database named stby.
You cannot use a single control file for both the primary and standby databases. They
must each have their own file.
Note: If a control file backup is taken on the primary and restored on a standby (or
vice versa), then the location of the snapshot control file on the restored system is configured
to be the default. (The default value for the snapshot control file name is platform-specific and
dependent on Oracle home.) Manually reconfigure it to the correct value using the RMAN CONFIGURE
SNAPSHOT CONTROLFILE command.
Step 8: Create a Parameter File for the Standby Database
Perform the following steps to create a parameter file for the standby database.
8.1 Create a parameter file (PFILE) from the server parameter file (SPFILE) used by the
primary database.
SQL> create pfile='/tmp/initstby.ora' from spfile;
File created.
8.2 Modify the parameter values in the parameter file created in the previous step.
Although most of the initialization parameter settings in the parameter file are also
appropriate for the physical standby database, some modifications must be made.
# The lines start without '*' is new added.
audit_file_dest='/u01/app/oracle/admin/stby/adump'
*.audit_trail='db'
*.compatible='12.2.0'
control_files='/u01/app/oracle/oradata/stby/control01.ctl','/u01/app/oracle/recovery_area/stby/control02.ctl'
*.db_block_size=8192
DB_FILE_NAME_CONVERT='/orcl/','/stby/'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/recovery_area'
*.db_recovery_file_dest_size=8016m
db_unique_name=stby
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
FAL_SERVER=orcl
FAL_CLIENT=stby
---->*.local_listener='LISTENER_ORCL' # must be removed
*.log_archive_config='DG_CONFIG=(orcl,stby)'
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=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_format='%t_%s_%r.arc'
LOG_FILE_NAME_CONVERT='/orcl/','/stby/'
*.memory_target=1570m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'
Ensure the COMPATIBLE initialization parameter is set to the same value on both the
primary and standby databases. If the values differ, then redo transport services may
be unable to transmit redo data from the primary database to the standby databases.
It is always a good practice to use the SHOW PARAMETERS command to verify no other
parameters need to be changed.
Step 9: Configure and start a listener on the primary system if one is not already configured.
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=orcl)
(GLOBAL_DBNAME=orcl.xxx.com)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
)
(SID_DESC=
(SID_NAME=stby)
(GLOBAL_DBNAME=stby.xxx.com)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
Step 10: Create Oracle Net service names
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL = #generated automatically
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)
Step 11: Copy Files from the Primary System to the Standby System
Ensure that all required directories are created and use an operating system copy
utility to copy the following binary files from the primary system to their correct
locations on the standby system:
Parameter File
[oracle@db ~]$ scp /tmp/initstby.ora oracle@192.168.10.20:/u01/app/oracle/product/12.2.0/dbhome_1/dbs
oracle@192.168.10.20's password:
initstby.ora 100% 1077 1.1KB/s 00:00
Backup File
[oracle@db ~]$ scp /u01/app/oracle/recovery_area/ORCL/backupset/2017_12_01/o1_mf_nnndf_FOR_STBY_f21wgkrc_.bkp
oracle@192.168.10.20:/u01/app/oracle/oradata/tmp
oracle@192.168.10.20's password:
o1_mf_nnndf_FOR_STBY_f21wgkrc_.bkp 100% 1.1GB 10.1MB/s 02:10
Control File
[oracle@db ~]$ scp /tmp/stby.ctl oracle@192.168.10.20:/u01/app/oracle/oradata/tmp
oracle@192.168.10.20's password:
stby.ctl 100% 10MB 10.1MB/s 00:00
Password File
[oracle@db ~]$ scp /u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl oracle@192.168.10.20:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/
oracle@192.168.10.20's password:
orapworcl 100% 3584 3.5KB/s 00:00
Then we change our password file for standby database as
[oracle@standby dbs]$ mv orapworcl orapwstby
------>Standby Database<------
Step 12: Create new directories for new database data files and control files as
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/oradata/stby
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/recovery_area/stby
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/admin/stby/adump
Step 13: Set Up the Environment to Support the Standby Database
Perform the following steps to create a Windows-based service, create a password file,
set up the Oracle Net environment, and create a SPFILE.
13.1 If the standby database is going to be hosted on a Windows system, then use the
ORADIM utility to create a Windows service. For example:
WINNT> oradim –NEW –SID boston –STARTMODE manual
13.2 Copy the remote login password file from the primary database system to the standby database system.
This step is optional if operating system authentication is used for administrative
users and if SSL is used for redo transport authentication. If not, then copy the
remote login password file from the primary database to the appropriate directory
on the physical standby database system.
We use second method here as in Step 11.
13.3 Configure and start a listener on the standby system if one is not already configured.
[oracle@standby admin]$ pwd
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=orcl)
(GLOBAL_DBNAME=orcl.xxx.com)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
)
(SID_DESC=
(SID_NAME=stby)
(GLOBAL_DBNAME=stby.xxx.com)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
13.4 Create Oracle Net service names
(If we want to use hostname then we have to add each hostname in '/etc/hosts' for both machine)
[oracle@standby ~]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/
[oracle@standby admin]$ cat tnsnames.ora
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
We can check with tnsping from each machine as
[oracle@db ~]$ tnsping stby
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 08-DEC-2017 18:38:34
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stby)))
OK (0 msec)
[oracle@standby admin]$ tnsping orcl
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 01-DEC-2017 15:44:40
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
13.5 On an idle standby database, use the SQL CREATE statement to create a server parameter file for the standby
database from the text initialization parameter file that was edited in Step 8.
[oracle@stand dbs]$ mv /tmp/initst.ora /u01/app/oracle/product/12.2.0/dbhome_1/dbs
SQL> create spfile from pfile;
File created.
13.6 If the primary database has a database encryption wallet, then copy it to the
standby database system and configure the standby database to use this wallet.
Note: The database encryption wallet must be copied from the primary database
system to each standby database system whenever the master encryption key is updated.
Encrypted data in a standby database cannot be accessed unless the standby
database is configured to point to a database encryption wallet or hardware
security module that contains the current master encryption key from the primary database.
Step 14: Add Standby Database parameters to system file for oracle which is used for ora
environment.
[oracle@standby admin]$ vi /etc/oratab
stby:/u01/app/oracle/product/12.2.0/dbhome_1:N
Step 15: Start the Physical Standby Database
Here we assume that all required files (control file, backup files) transferred from primary are located in /tmp directory)
Do followings to mount standby database.
oracle@standby admin]$ rman target=/
RMAN> startup nomount;
RMAN> restore controlfile from '/tmp/stby.ctl';
RMAN> alter database mount;
RMAN> catalog start with '/tmp/o1_mf_nnndf_FOR_STBY_f21wgkrc_.bkp';
RMAN> restore database;
RMAN> recover database;
Step 16: Add Standby Redo Logs
Because we use transport mode - Log Writer (LGWR) we have to add standby redo logs.
Note: If neither the ARCH or LGWR attribute is specified, the default is ARCH.
How many standby redo log file do we need to add???
It is recommended that we have 1 more standby redo log file than the source (primary) database at least with the same size.
We have 3 redo logs which type 'ONLINE'. So we will add 4 standby redo log for standby datbase.
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
Database altered.
SQL> select group#, type, status from v$logfile order by group#;
GROUP# TYPE STATUS
--------- ------- -------
1 ONLINE
2 ONLINE
3 ONLINE
4 STANDBY
5 STANDBY
6 STANDBY
7 STANDBY
Step 17: Issue the following command to start Redo Apply
SQL> alter database recover managed standby database disconnect from session;
Database altered.
The statement includes the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session.
To stop Redo Apply
SQL> alter database recover managed standby database cancel;
Step 18: Verify the Physical Standby Database Is Performing Properly
Query the V$MANAGED_STANDBY view to verify that redo is being transmitted from the primary database and applied to the standby database.
SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR RFS 1 33 IDLE
N/A MRP0 1 33 APPLYING_LOG
The query output should show one line for the primary database with a CLIENT_PROCESS of LGWR. This indicates that redo
transport is functioning correctly and the primary redo thread is being sent to the standby.
SQL> select thread#, sequence#, applied from v$archived_log;
THREAD# SEQUENCE# APPLIED
--------- ---------- ---------
1 54 YES
1 55 YES
1 56 IN-MEMORY
No comments:
Post a Comment