preface

After many requests from friends in the exchange group, I'd like to share the ADG building tutorial of RAC to Single!

1, Environmental preparation

Old rules, test environment, practical demonstration:

📢 The following points should be noted:

• db_ unique_ The name of the primary and standby databases cannot be the same
• db_name the primary and standby databases must be consistent
• The DB versions of the primary and standby databases must be consistent

2, Construction process

1. Oracle Software installation

Use the Oracle one click installation script I wrote to quickly install the main database RAC and the standby database stand-alone.

cd ../racdb
vagrant up
vagrant ssh node1
su - root
cd /soft
sh rac_install.sh

Simply wait for a period of time and Oracle RAC is installed successfully!

2. Environment configuration

Before setting up ADG, you need to configure the environment information, including hostname resolution and TNS.

1. Configure hosts file

Main library:

##Node i
#dg
cat<<EOF>>/etc/hosts
10.211.55.110 luciferdg
EOF

##Node II
#dg
cat<<EOF>>/etc/hosts
10.211.55.110 luciferdg
EOF

Spare warehouse:

##dg
cat<<EOF>>/etc/hosts
10.211.55.100 lucifer01
10.211.55.101 lucifer02
10.211.55.105 lucifer-scan
EOF

2. Configure TNS

Primary database + standby database, executed under root user:

##tnsnames.ora
su - oracle -c "cat <<EOF >> /u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora
##FOR DG BEGIN
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lucifer-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCL1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lucifer01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lucifer02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
ORCLDG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = luciferdg)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
##FOR DG BEGIN
EOF"

3. The main library starts archiving and forced logging

Enable archive mode (shutdown required):

srvctl stop database -d orcl -o immediate
srvctl start instance -d orcl -i orcl1 -o mount
alter database archivelog;
alter database open;
srvctl start instance -d orcl -i oorcl2

Enable forced log mode (online):

alter database force logging;

4. Copy the parameter file and password file to the standby database

Copy the parameter file to the standby database (executed by the standby database). To copy it under oracle user:

su - oracle
scp oracle@lucifer01:/tmp/initorcldg.ora /tmp

Create directory of standby database:

mkdir -p /u01/app/oracle/admin/orcl/adump
su - oracle -c "mkdir -p /oradata/orcl/datafile"
su - oracle -c "mkdir -p /oradata/orcl/onlinelog"
su - oracle -c "mkdir -p /oradata/orcl/tempfile"
mkdir -p /u01/app/oracle/fast_recovery_area/orcl
chown -R oracle:oinstall /u01/app/oracle/admin/orcl/adump
chown -R oracle:oinstall /u01/app/oracle/fast_recovery_area

Parameter file modified by standby database:

*._optimizer_cartesian_enabled=FALSE
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.control_files='/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_create_file_dest='/oradata/orcl'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=5501878272
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.event='10949 trace name context forever:28401 trace name context forever,level 1:10849 trace name context forever, level 1:19823 trace name context forever, level 90'
*.open_cursors=300
*.pga_aggregate_target=196083712
*.processes=150
*.result_cache_max_size=0
*.sga_target=784334848
*.db_unique_name='orcldg'
*.log_archive_config='dg_config=(ORCLDG,ORCL)'
*.log_archive_dest_1='location=/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=ORCLDG'
*.log_archive_dest_2='service=orcl async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.remote_login_passwordfile='exclusive'
*.fal_server='ORCL'
*.fal_client='ORCLDG'
*.db_file_name_convert='+DATA','/oradata'
*.log_file_name_convert='+DATA','/oradata'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Copy the password file to the standby database (to be executed by the standby database). To copy it under oracle user:

su - oracle
scp oracle@lucifer01:/u01/app/oracle/product/11.2.0/db/dbs/orapworcl1 /u01/app/oracle/product/11.2.0/db/dbs/orapworcl

5. Adding standby log files to the main library

set pagesize100
set line222
col member for a60
select * from v$logfile;
select * from v$log;

📢 be careful:

• The size of standby log is consistent with that of redo log
• Number of standby logs: standby logfile = (1 + number of logfile groups) * thread=(1+3)*1=4 groups. 4 groups of standby logfiles need to be added
• The thread should be consistent with the redo log. In case of rac, the standby log corresponding to multiple threads needs to be added

ALTER DATABASE ADD STANDBY LOGFILE thread 1 
group 5 ('+DATA') SIZE 120M,
group 6 ('+DATA') SIZE 120M,
group 7 ('+DATA') SIZE 120M;

ALTER DATABASE ADD STANDBY LOGFILE thread 2
group 8 ('+DATA') SIZE 120M,
group 9 ('+DATA') SIZE 120M,
group 10 ('+DATA') SIZE 120M;

select * from v$standby_log;

6. Enable the standby database to the unmount state

startup nomount pfile='/tmp/initorcldg.ora';

3,RMAN DUPLICATE

rman recovery standby database:

rman target sys/oracle@orcl1 AUXILIARY sys/oracle@orcldg
 
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
}

Setting ADG parameters for main library:

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCLDG)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcldg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDG';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=ORCLDG;
ALTER SYSTEM SET FAL_CLIENT=ORCL;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata','+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata','+DATA'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

4. Enable log application for standby database

-- First, open the standby database to read-only status
alter database open read only;
-- Start the active / standby synchronization process
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
-- Check standby database role
select database_role,open_mode from v$database;
   
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
-- Check protection mode, maximum performance
SQL> SELECT protection_mode FROM v$database;
 
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

5. Main library open log_ ARCHIVE_ DEST_ STATE_ two

Confirm that there is no problem before the active and standby databases. The main database starts synchronization:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Write at the end

The construction process of ADG is relatively simple. Many friends often ask about the tutorials from stand-alone to stand-alone, RAC to stand-alone, RAC to RAC. In fact, there is little difference between these construction methods. You only need to modify some parameters!