1. Environmental preparation

	Main library	Spare database
operating system	Linux	Linux
Server name	primarynode	standbynode
IP address planning	192.168.186.130	192.168.186.131
---------------	-----------------------	----------------
Database version	11.2.0.4	11.2.0.4
db_name	sfxt	sfxt
db_unique_name	sfxt_bf	sfxt_bf
instance_name	sfxt	sfxt_bf
service_name	sfxt	sfxt
Database installation	Install database software + create listening + install database	Install database software + create monitor

2. Environment configuration

1. Modify node name:

vim /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 
::1         localhost localhost.localdomain localhost6
192.168.186.130  primarynode
192.168.186.131  standbynode

2. Main library environment variable configuration

PATH=$PATH:$HOME/bin

ORACLE_SID=sfxt; export ORACLE_SID
ORACLE_BASE=/data/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin; export PATH
export TEMP=/tmp
export TMPDIR=/tmp
umask 022

export PATH

3. Slave environment variable configuration

PATH=$PATH:$HOME/bin

ORACLE_SID=sfxt_bf; export ORACLE_SID
ORACLE_BASE=/data/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin; export PATH
export TEMP=/tmp
export TMPDIR=/tmp
umask 022

export PATH

3. Main library configuration

3.1 enable the forced logging function

• Query whether forced logging is enabled

select force_logging from v$database;

• If not enabled, use the following statement to turn on forced logging

alter database force logging;  --Force logging

3.2 enable archiving

• Query whether archiving is enabled

archive log list 
--perhaps
select log_mode from v$database;

• If archiving is not enabled, you can start archiving as follows

[](javascript:void(0)😉

----Oracle 11g Database archiving mode on

--STEP1: with sysdba Role login database
sqlplus / as sysdba

--STEP2: Clean shutdown of database
shutdown immediate

--STEP3: Start database to mount state
startup mount

--STEP4: Open archive
alter database archivelog;

--STEP5: Open database
alter database open

[](javascript:void(0)😉

3.3 main warehouse parameter configuration

• db_unique_name

SQL> alter system set db_unique_name = 'sfxt' scope=spfile;

• log_archive_config

SQL> alter system set log_archive_config='DG_CONFIG=(sfxt,sfxt_bf)' scope=spfile;

• log_archive_dest_1

SQL> alter system set log_archive_dest_1='LOCATION=/data/oracle/product/11.2.0/db_1/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sfxt' scope=both;

• log_archive_dest_2

SQL> alter system set log_archive_dest_2='SERVICE=tnssfxt_bf LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sfxt_bf' scope=spfile;

• log_archive_dest_state_1

alter system set log_archive_dest_state_1 = ENABLE;

• log_archive_dest_state_2

alter system set log_archive_dest_state_2 = ENABLE;

• db_file_name_convert to view the location of the data file:

SQL> select name from v$datafile;

• If the data file locations of the primary database and the standby database are different, you need to use db_file_name_convert to convert.

SQL> alter system set db_file_name_convert='/data/oracle/oradata/sfxt_bf','/data/oracle/oradata/sfxt' scope=spfile;

• log_file_name_convert to view the location of the online log file:

SQL> select member from v$logfile;

• If the online log file locations of the primary database and the standby database are different, you need to use log_file_name_convert to convert.

SQL> alter system set log_file_name_convert='/data/oracle/oradata/sfxt_bf','/data/oracle/oradata/sfxt' scope=spfile;

• standby_file_management

SQL> alter system set standby_file_management=auto scope=spfile;

• fal_client

SQL> alter system set fal_client='tnssfx' scope=both;

• fal_server

SQL> alter system set fal_server='tnssfxt_bf' scope=both;

3.4 main library static monitoring configuration

[oracle@primarynode admin]$ vim listener.ora

# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
 (DESCRIPTION =
   (ADDRESS_LIST = 
     (ADDRESS = (PROTOCOL = TCP)(HOST = primarynode)(PORT = 1521))
   )
 )
)

SID_LIST_LISTENER = 
(SID_LIST = 
 (SID_DESC = 
   (GLOBAL_DBNAME=sfxt)
   (SID_NAME = sfxt)
   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)       
 ) 
) 

ADR_BASE_LISTENER = /u01/oracle

3.5 main database tnsnames.ora file configuration

vim tnsnames.ora

tnssfxt =
(DESCRIPTION =
 (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.130)(PORT = 1521))
 )
 (CONNECT_DATA =
   (SERVICE_NAME = sfxt)
 )
)

tnssfxt_bf =
(DESCRIPTION =
 (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.131)(PORT = 1521))
 )
 (CONNECT_DATA =
   (SERVICE_NAME = sfxt)
 )
)

4. Standby database configuration

4.1 copy password file from master database

Log in to the primary database and copy the password file to the secondary database

[oracle@primarynode ~]$ cd $ORACLE_HOME/dbs
[oracle@primarynode dbs]$ scp orapwsfxt 192.168.186.131:/data/oracle/product/11.2.0/db_1/dbs

Log in to the standby database and modify the name of the password file

mv orapwsfxt orapwsfxt_bf

4.2 initialization parameter file

• Generate initialization parameter file in main library

  SQL> create pfile from spfile;
  

• Copy the parameter file of the primary library to the secondary library and rename it

  Perform a copy operation on the primary library

  [oracle@primarynode dbs]$ scp initsfxt.ora 192.168.186.131:/data/oracle/product/11.2.0/db_1/dbs
  

  Rename on the standby database

  [oracle@standbynode dbs]$ mv initsfxt.ora initsfxt_bf.ora
  

• Modify the parameter file content of the standby database

  db_name='sfxt'
  db_unique_name='sfxt_bf'
  audit_file_dest='/data/oracle/admin/sfxt_bf/adump'
  compatible='11.2.0.4.0'
  control_files='/data/oracle/oradata/sfxt_bf/control01.ctl','/data/oracle/flash_recovery_area/sfxt_bf/control02.ctl'
  log_archive_config='DG_CONFIG=(sfxt,sfxt_bf)'
  log_archive_dest_1='LOCATION=/data/oracle/product/11.2.0/db_1/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sfxt_bf'
  log_archive_dest_2='SERVICE=tnssfxt LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sfxt'
  db_file_name_convert='/data/oracle/oradata/sfxt','/data/oracle/oradata/sfxt_bf'
  log_file_name_convert='/u01/app/oracle/oradata/sfxt','/u01/app/oracle/oradata/sfxt_bf'
  fal_client='tnssfxt_bf'
  fal_server='tnssfxt'
  standby_file_management='AUTO'
  

• Create a new directory

  mkdir -p /data/oracle/admin/sfxt_bf/adump
  mkdir -p  /data/oracle/flash_recovery_area/sfxt_bf
  

• Copy the control file

  Master library create control file

  ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'sfxt_bf.ctl';
  

  Copy to and from library

  scp oracle@192.168.186.130:/data/oracle/product/11.2.0/db_1/dbs/sfxt_bf.ctl /data/oracle/oradata/sfxt_bf/control01.ctl
  

4.3 creating spfile using pfile file

Log in to the idle database

sqlplus / as sysdba

Create spfile

create spfile from pfile

Start the database to the unmount state

startup nomount

4.4 configure static listening

[oracle@standbynode admin]$ vim listener.ora

# listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
 (DESCRIPTION =
   (ADDRESS_LIST = 
     (ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521))
   )
 )
)

SID_LIST_LISTENER = 
(SID_LIST = 
 (SID_DESC = 
   (GLOBAL_DBNAME=sfxt)
   (SID_NAME = sfxt_bf)
   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)       
 ) 
) 

ADR_BASE_LISTENER = /data/oracle

4.5 configure the tnsnames.ora file and directly copy the main database

4.6 test network connectivity

Execute on both primary and standby databases to ensure normal access

tnsping tnssfxt
tnsping tnssfxt_bf

In the main library:

sqlplus sys/sys123@tnssfxt as sysdba
sqlplus sys/sys123@tnssfxt_bf as sysdba

4.7 parameter check (optional)

1.db_unique_name       : 2 Nodes need to be different
2.compatible           : The compatibility between the primary database and the standby database shall be consistent
3.log_archive_config      : Configure the between primary and standby databases db_unique_name
4.log_archive_dest_1,2    : Path to archive log
5.log_archive_dest_state_2 :
enable -- Enable log_archive_dest_2
defer  --Disable log_archive_dest_2
6.db_file_name_convert    : Data file conversion path
7.log_file_name_convert   : Log file conversion path
8.standby_file_management : Set to auto
9.log_archive_format     : The log file format shall be consistent on both sides

5. Use duplicate to create a physical standby

5.1 connecting to primary and standby databases

Standby database side execution:

## nocatalog must be added, or an error will be reported when duplicate is executed
[oracle@primarynode ~]$ rman target sys/sys123@sfxt auxiliary sys/sys123@sfxt_bf nocatalog

5.2 using duplicate to copy database

RMAN> duplicate target database for standby from active database nofilenamecheck;

6. Add the standby log group of the primary database and the standby database

• Number of standby log groups: configured as number of redo log groups + 1
• Add the standby log group in both the primary and standby databases. The primary library may not be added, but if the active / standby switch occurs later, it still needs to be added, so it is best to add it at one time.
• Query only the standby log group: select * from v$standby_log ;

6.1 add the standby log group of the main library

alter database add standby logfile group 4 ('/data/oracle/oradata/sfxt/stredo04.log') size 50M;
alter database add standby logfile group 5 ('/data/oracle/oradata/sfxt/stredo05.log') size 50M;
alter database add standby logfile group 6 ('/data/oracle/oradata/sfxt/stredo06.log') size 50M;
alter database add standby logfile group 7 ('/data/oracle/oradata/sfxt/stredo07.log') size 50M;

6.2 add the standby log group of the standby database

alter database add standby logfile group 4 ('/data/oracle/oradata/sfxt_bf/stredo04.log') size 50M;
alter database add standby logfile group 5 ('/data/oracle/oradata/sfxt_bf/stredo05.log') size 50M;
alter database add standby logfile group 6 ('/data/oracle/oradata/sfxt_bf/stredo06.log') size 50M;
alter database add standby logfile group 7 ('/data/oracle/oradata/sfxt_bf/stredo07.log') size 50M;

7. Start synchronizing the database (execute on the standby database)

alter database open;  

--Method 1: enable real-time synchronization
alter database recover managed standby database using current logfile disconnect from session;
--Or abbreviated as:
alter database recover managed standby database using current logfile disconnect;

--Mode 2: enable synchronization (only when the log is switched)
alter database recover managed standby database disconnect from session;

8. Check whether the implementation is successful

8.1 main library status viewing

SQL> select    open_mode,      --Database on mode. If real-time synchronization is available, it is: read only with apply，To cancel synchronization: read only
          database_role,       --Database role, primary or standby
          protection_mode,     --Protection mode
          protection_level     --Protection level
from  v$database;  
SQL> select open_mode,database_role,protection_mode,protection_level from  v$database;  

OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE       PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ WRITE           PRIMARY          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

8.2 viewing standby database status

SQL> select    open_mode,          
          database_role,      
          protection_mode,     
          protection_level     
from  v$database;   
OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE       PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE