“This is the 17th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”
See: Data Guard Physical Standby Setup in Oracle Database 11g Release 2
MOS documentation: Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE… FROM ACTIVE DATABASE (Doc ID 1075908.1)
Official documentation: Duplicating Databases
Active Database Duplicate step (Using Image Copies)
1. Set the DG parameter for the primary library and set the parameter value for the secondary library based on the PFILE of the primary library to generate spfile.2 for the secondary library. Configure the hosts file, TNS, static listening, and standby log file. 3. Copy the password file from the primary database to the standby database. The standby database creates a directory that does not exist in PFILE. 5.RMAN connects the primary and secondary libraries at the same time and runs the duplicate command.Copy the code
Note: Active Database Duplicate does not need to back up Database files in advance. Instead, it copies Database files online over the network, which has high load requirements on the CPU of the primary Database. Therefore, it is best to perform this operation in idle time. Using Active Duplicate to build DG is efficient and saves space, but has high requirements on network. The source library must use SPFILE.
Two, environmental preparation
| The host name | ip | DB Version | db_name | db_unique_name | |
| The main library | orcl | 192.168.56.120 | 11.2.0.4 | orcl | orcl |
| For the library | orcl_stby | 192.168.56.121 | 11.2.0.4 | orcl | orcl_stby |
Notes:
1. Db_unique_name The active and standby databases must be different.
2. The db_name must be the same as that of the active and standby databases.
3. The DB versions of the active and standby databases must be consistent.
3. Construction process
1. Oracle software installation
Master library one-click installation:
./ alloraclesilent. sh -i 192.168.56.120 -d 11g -n orcl -o orCL -b /u01/app -s AL32UTF8Copy the code
One-click installation for the standby database :(only ORACLE software is installed for the standby database, not database construction)
./ alloraclesilent. sh -i 192.168.56.121 -d 11g -w Y -n orcl_stby -o orcl -b /u01/app -s AL32UTF8Copy the code
One-click installation script for reference: One-click ORACLE 11G/12C/18C/19C stand-alone installation script and database construction
2. Environment configuration
A. Configure the hosts file
The main library:
cat <<EOF >> /etc/hosts
##FOR DG BEGIN
192.168.56.121 orcl_stby
##FOR DG END
EOF
Copy the codeFor library:
cat <<EOF >> /etc/hosts
##FOR DG BEGIN
192.168.56.120 orcl
##FOR DG END
EOF
Copy the codeB. Configure static listening and TNS
Master library + Standby library:
Notes: Note that GLOBAL_DBNAME is the same as service_name, that is, orcl_stby is the standby database.
##listener.ora
su - oracle -c "The cat < < EOF > > / u01 / app/oracle/product / 11.2.0 / db/network/admin/listener. Ora # # FOR DG BEGIN SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = former) (ORACLE_HOME = / u01 / app/oracle/product / 11.2.0 / db) (SID_NAME = former))) # # FOR DG END EOF"
# restart listener
su - oracle -c "lsnrctl stop"
su - oracle -c "lsnrctl start"
##tnsnames.ora
su - oracle -c "Cat < < EOF > > / u01 / app/oracle/product / 11.2.0 / db/network/admin/tnsnames. Ora # # FOR DG BEGIN former = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ORCL_STBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl_stby)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl_stby) ) ) ##FOR DG BEGIN EOF"
Copy the codeC. Configure primary library parameters
select log_mode,force_logging from gv$database;
LOG_MODE FOR
------------ ---
NOARCHIVELOG NO
-- Enable the archive mode
shutdown immediate
startup mount
alter database archivelog;
alter database open;
-- Enable the forced log mode
alter database force logging;
- Check whether the data file paths are consistent. You are advised to disable OMF
select name from v$datafile;
show parameter db_create_file_dest
alter system reset db_create_file_dest;
--NOTES: If the data file path is inconsistent, Duplicate will fail.
-- Set the DG parameter
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STBY)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl_stby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
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=ORCL_STBY;
ALTER SYSTEM SET FAL_CLIENT=ORCL;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata/orcl'.'/oradata/orcl' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata/orcl'.'/oradata/orcl' SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Copy the codeD. Generate the pfile file of the standby database and modify and copy the parameter file and password file to the standby database
create pfile='/tmp/initorcl_stby.ora'from spfile; Db_unique_name =orcl_stby *.fal_client='ORCL_STBY'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(ORCL_STBY,ORCL)'
*.log_archive_dest_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL_STBY'
*.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'Ora/TMP -- To copy the password file to the standby database, run the su - oracle SCP command as the oracle user Oracle @ former: / u01 / app/oracle/product / 11.2.0 / db/DBS/orapworcl/u01 / app/oracle/product / 11.2.0 / db/DBSCopy the codeE. Add the Stanby log file to the primary library
set line222
col member for a60
select t2.thread#,t1.group#,t1.member,t2.bytes/1024/1024 from gv$logfile t1,gv$log t2 where t1.group#=t2.group#;
THREAD# GROUP# MEMBER T2.BYTES/1024/1024
---------- ---------- ------------------------------------------------------------ ------------------
1 3 /oradata/orcl/redo03.log 120
1 2 /oradata/orcl/redo02.log 120
1 1 /oradata/orcl/redo01.log 120
-- Note:
--1. The size of stanby log is the same as that of redo log
--2. Stanby log quantity:
standby logfile=(1+Logfile group number)*thread=(1+3)*1=4Group, need to add4Set of standby logfile.If the log is rac, add a standby log for each thread
ALTER DATABASE ADD STANDBY LOGFILE thread 1
group 4 ('/oradata/orcl/standby_redo04.log') SIZE 120M,
group 5 ('/oradata/orcl/standby_redo05.log') SIZE 120M,
group 6 ('/oradata/orcl/standby_redo06.log') SIZE 120M,
group 7 ('/oradata/orcl/standby_redo07.log') SIZE 120M;
Copy the codeF. Create a DB directory for the standby database and enable it to the nomount state
su - oracle -c "mkdir -p /oradata/orcl"
su - oracle -c "mkdir -p /u01/app/oracle/fast_recovery_area/orcl"
su - oracle -c "mkdir -p /u01/app/oracle/admin/orcl/adump"
sqlplus / as sysdba
create spfile from pfile='/tmp/initorcl_stby.ora';
startup nomount
Copy the code3、 RMAN DUPLICATE
rman target sys/oracle@orcl AUXILIARY sys/oracle@orcl_stby
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;
}
Copy the code4. Enable the log application for the standby database
alter database open read only;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
SQL> SELECT protection_mode FROM v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
Copy the code5. Enable LOG_ARCHIVE_DEST_STATE_2 for the primary database
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
Copy the code6. Test synchronization
set line222
col member for a60
-- Check whether RFS and MRP processes exist
select process,group#,thread#,sequence# from gv$managed_standby;
-- Check whether the standby log status is active
select t1.group#,t1.thread#,t1.bytes/1024/1024,t1.status,t2.member from gv$standby_log t1,gv$logfile t2 where t1.group#=t2.group#;
Create table space, create user, create table, add, delete, modify test
create tablespace TEST datafile '/oradata/orcl/test01.dbf' size 100M autoextend off;
create user test identified by test;
grant dba to test;
conn test/test
create table test(id number);
insert into test values (1);
insert into test values (2);
commit;
-- Check whether synchronization is performed on the standby database
SQL> select tablespace_name from dba_tablespaces where tablespace_name='TEST';
TABLESPACE_NAME
------------------------------
TEST
SQL> select username,account_status,created from dba_users where username='TEST';
USERNAME ACCOUNT_STATUS CREATED
------------------------------ -------------------------------- ------------------
TEST OPEN 17-APR- 21
SQL> select * from test.test;
ID
----------
1
2
Copy the code