Using Broker to Setup Oracle Database 11.2 RAC DataGuard Physical Standby

Oracle Database - 11.2.0.4.0 RAC

Introduction

主备数据库都是 Oracle 11gR2 RAC 环境，数据库文件目录架构一致。采用 Maximum Performance 方式，即日志同步采取 ASYNC noaffirm 方式。

	主库	备库
操作系统	Redhat 7.9	Redhat 7.9
主机名	oracle11grac01,oracle11grac02	oracle11grac03,oracle11grac04
数据库版本	11.2.0.4	11.2.0.4
集群版本	11.2.0.4	11.2.0.4
DB_NAME	coco	coco
DB_UNIQUE_NAME	cocoprim	cocostby
instance_name	coco1, coco2	coco1, coco2
SID_NAME	coco	coco
ORACLE_HOME(GI)	/u01/app/grid	/u01/app/grid
ORACLE_HOME(DB)	/u01/app/oracle/product/11.2.0/db_1	/u01/app/oracle/product/11.2.0/db_1
数据存储方式	ASM	ASM
文件管理	Common Location	Common Location
数据库文件目录	+DATA	+DATA
归档日志目录	+RECO	+RECO
监听端口	1521	1521

Setting on Primary Database

Enable archiving and forced logging on the primary database.

SQL> archive log list
SQL> alter database force logging;
SQL> select name,log_mode,force_logging from gv$database;
NAME      LOG_MODE     FOR
--------- ------------ ---
COCO      ARCHIVELOG   YES
COCO      ARCHIVELOG   YES

Set all necessary initialization parameters on the primary database.

SQL> alter system set db_unique_name='cocoprim' scope=spfile sid='*';
SQL> alter system set log_archive_config='DG_CONFIG=(cocoprim,cocostby)' scope=both sid='*';
SQL> alter system set log_archive_dest_2='SERVICE=cocostby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cocostby' scope=both sid='*';
SQL> alter system set log_archive_dest_state_2='enable' scope=both sid='*';
SQL> alter system set fal_server='cocostby' scope=both sid='*';
SQL> alter system set fal_client='cocoprim' scope=both sid='*';
SQL> alter system set db_file_name_convert='cocoprim','cocostby' scope=spfile sid='*';
SQL> alter system set log_file_name_convert='cocoprim','cocostby' scope=spfile sid='*';
SQL> alter system set standby_file_management=AUTO scope=both sid='*'

Verify that the values are set correctly for these parameters after bouncing the database.

SQL> set linesize 500 pages 0
col value for a90
col name for a50
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','fal_client',
               'db_file_name_convert','log_file_name_convert', 'standby_file_management');

~]$ srvctl remove database -d coco
~]$ srvctl add database -d cocoprim -n coco -o /u01/app/oracle/product/11.2.0/db_1 -p +DATA/coco/spfilecoco.ora -r primary -a DATA,RECO
~]$ srvctl add instance -d cocoprim -i coco1 -n oracle11grac01
~]$ srvctl add instance -d cocoprim -i coco2 -n oracle11grac02
~]$ srvctl config database -d cocoprim
~]$ srvctl start database -d cocoprim

Configure Oracle Net, as required, to allow connections to the standby database.

~]$ cat > /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora << EOF
COCOPRIM =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = cocoprim)
    )
)

COCOPRIM1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11grac01-vip)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = cocoprim)
    (SID = coco1)
    )
)

COCOPRIM2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11grac02-vip)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = cocoprim)
    (SID = coco2)
    )
)

COCOSTBY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = newrac-scan)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = cocostby)
    )
)

COCOSTBY1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11grac03-vip)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = cocostby)
    (SID = coco1)
    )
)

COCOSTBY2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11grac04-vip)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = cocostby)
    (SID = coco2)
    )
)

EOF

Create an initialization parameter file for the standby database.

SQL> create pfile='/tmp/pfile_for_stby.ora' from spfile;

~]$ scp /tmp/pfile_for_stby.ora oracle11grac03:/tmp/pfile_for_stby.ora

Copy the remote login password file from the primary database system to the standby database system.

~]$ scp $ORACLE_HOME/dbs/orapw* oracle11grac03:$ORACLE_HOME/dbs/orapwcoco1
~]$ scp $ORACLE_HOME/dbs/orapw* oracle11grac04:$ORACLE_HOME/dbs/orapwcoco2

Setting on Standby Database

Create standby database directory on ASM.

rm -rf +DATA/*
rm -rf +RECO/*
mkdir +DATA/COCO
mkdir +RECO/COCO
cd +DATA/COCO
mkdir PARAMETERFILE DATAFILE CONTROLFILE TEMPFILE ONLINELOG
cd +RECO/COCO
mkdir ARCHIVELOG CONTROLFILE ONLINELOG

Create audit directory.

~]$ mkdir -p $ORACLE_BASE/admin/coco/adump

Configure static listeners for the standby database.

~]$ cat >> /u01/app/11.2.0/grid/network/admin/listener.ora << EOF
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = cocostby)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    (SID_NAME = coco1)
    )
)

ADR_BASE_LISTENER = /u01/app/grid
EOF

~]$ lsnrctl reload

Configure Oracle Net, as required, to allow connections to the primary database.

~]$ cat >> /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora << EOF
COCO =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SID_NAME = coco)
    )
)

COCO1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11grac01-vip)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SID_NAME = coco)
    )
)

COCO2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11grac02-vip)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SID_NAME = coco)
    )
)

COCOSTBY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = newrac-scan)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SID_NAME = cocostby)
    )
)

COCOSTBY1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11grac03-vip)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SID_NAME = cocostby)
    )
)

COCOSTBY2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11grac04-vip)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SID_NAME = cocostby)
    )
)

EOF

Modify initialization parameter file.

cat > /tmp/pfile.ora << EOF
coco1.__db_cache_size=3087007744
coco2.__db_cache_size=3053453312
coco2.__java_pool_size=16777216
coco1.__java_pool_size=16777216
coco2.__large_pool_size=33554432
coco1.__large_pool_size=33554432
coco2.__pga_aggregate_target=1325400064
coco1.__pga_aggregate_target=1325400064
coco2.__sga_target=3959422976
coco1.__sga_target=3959422976
coco2.__shared_io_pool_size=0
coco1.__shared_io_pool_size=0
coco1.__shared_pool_size=788529152
coco2.__shared_pool_size=822083584
coco2.__streams_pool_size=0
coco1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/coco/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/coco/control01.ctl','+RECO/coco/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='coco'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=4294967296
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cocoXDB)'
coco1.instance_number=1
coco2.instance_number=2
*.db_unique_name='cocostby'#for physical standby
*.fal_server='coco'#for physical standby
*.fal_client='cocostby'#for physical standby
*.log_archive_config='DG_CONFIG=(coco,cocostby)'#for physical standby
*.log_archive_dest_1='LOCATION=+RECO'
*.standby_file_management='auto'#for physical standby
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=1319108608
*.processes=150
*.remote_listener='newrac-scan:1521'#for physical standby
*.remote_login_passwordfile='exclusive'
*.sga_target=3958374400
coco2.thread=2
coco1.thread=1
coco1.undo_tablespace='UNDOTBS1'
coco2.undo_tablespace='UNDOTBS2'
EOF

DB_FILE_NAME_CONVERT is useful for creating a duplicate database for recovery purposes. It converts the filename of a new datafile on the primary database to a filename on the standby database.
LOG_FILE_NAME_CONVERT converts the filename of a new log file on the primary database to the filename of a log file on the standby database.

Start the standby database instance without mounting the control file.

~]$ sqlplus / as sysdba
SQL> startup nomount pfile='/tmp/pfile_for_stby.ora'

Creating a standby database with active database duplication.

~]$ rman target sys/oracle@coco1 auxiliary sys/oracle@cocostby1

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Feb 10 20:55:00 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: COCO (DBID=3082357723)
connected to auxiliary database: COCO (not mounted)

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

Starting Duplicate Db at 10-FEB-24
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 instance=coco1 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwcoco1' auxiliary format
 '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwcoco1'   ;
}
executing Memory Script

Starting backup at 10-FEB-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=76 instance=coco1 device type=DISK
Finished backup at 10-FEB-24

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '+DATA/coco/control01.ctl';
   restore clone controlfile to  '+DATA/coco/control02.ctl' from
 '+DATA/coco/control01.ctl';
}
executing Memory Script

Starting backup at 10-FEB-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_coco1.f tag=TAG20240210T205515 RECID=4 STAMP=1160600119
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 10-FEB-24

Starting restore at 10-FEB-24
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 10-FEB-24

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "+DATA/coco/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "+DATA/coco/system01.dbf";
   set newname for datafile  2 to
 "+DATA/coco/sysaux01.dbf";
   set newname for datafile  3 to
 "+DATA/coco/undotbs01.dbf";
   set newname for datafile  4 to
 "+DATA/coco/users01.dbf";
   set newname for datafile  5 to
 "+DATA/coco/undotbs02.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "+DATA/coco/system01.dbf"   datafile
 2 auxiliary format
 "+DATA/coco/sysaux01.dbf"   datafile
 3 auxiliary format
 "+DATA/coco/undotbs01.dbf"   datafile
 4 auxiliary format
 "+DATA/coco/users01.dbf"   datafile
 5 auxiliary format
 "+DATA/coco/undotbs02.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA/coco/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 10-FEB-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/coco/sysaux01.dbf
output file name=+DATA/coco/sysaux01.dbf tag=TAG20240210T205529
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/coco/system01.dbf
output file name=+DATA/coco/system01.dbf tag=TAG20240210T205529
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/coco/undotbs01.dbf
output file name=+DATA/coco/undotbs01.dbf tag=TAG20240210T205529
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/coco/undotbs02.dbf
output file name=+DATA/coco/undotbs02.dbf tag=TAG20240210T205529
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/coco/users01.dbf
output file name=+DATA/coco/users01.dbf tag=TAG20240210T205529
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 10-FEB-24

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1160600191 file name=+DATA/coco/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=1160600191 file name=+DATA/coco/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1160600191 file name=+DATA/coco/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1160600191 file name=+DATA/coco/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=1160600191 file name=+DATA/coco/undotbs02.dbf
Finished Duplicate Db at 10-FEB-24

In addition, copy the server parameter file from the source database to the duplicate database by specifying the SPFILE statement.

RMAN> duplicate target database
for standby
from active database
spfile
    set db_unique_name='cocostby'
    set control_files='+data/coco/control01.ctl','+reco/coco/control02.ctl'
    set fal_server='coco'
    set fal_client='cocostby'
    set remote_listener='newrac-scan:1521'
    set standby_file_management='auto'
    reset log_archive_dest_2
    reset log_archive_dest_state_2
nofilenamecheck;

Create standby online redo log on the standby database, standby redo log must have at least one more redo log group than the redo log at the redo source database.

SQL> set lines 200
col member for a50
select l.group#,l.thread#,l.status,l.bytes/1024/1024 "SIZE(M)",lf.member,lf.status from v$log l left join v$logfile lf on l.group#=lf.group# order by 1;

SQL> alter system set standby_file_management='manual' scope=both sid='*';

SQL> alter database add standby logfile thread 1 group 5 '+RECO' size 50M;
SQL> alter database add standby logfile thread 1 group 6 '+RECO' size 50M;
SQL> alter database add standby logfile thread 1 group 7 '+RECO' size 50M;
SQL> alter database add standby logfile thread 2 group 8 '+RECO' size 50M;
SQL> alter database add standby logfile thread 2 group 9 '+RECO' size 50M;
SQL> alter database add standby logfile thread 2 group 10 '+RECO' size 50M;

SQL> select sl.group#,sl.thread#,sl.status,sl.bytes/1024/1024 "SIZE(M)",lf.member,lf.status from v$standby_log sl left join v$logfile lf on sl.group#=lf.group# order by 1;

SQL> alter system set standby_file_management='auto' scope=both sid='*';

Start redo apply on the standby database.

SQL> alter system set log_archive_dest_state_2='enable' scope=both sid='*';    -- primary

SQL> alter database recover managed standby database using current logfile disconnect;

SQL> select a.thread, a.received, b.applied
  from (select thread# thread, max(sequence#) received
         from v$archived_log
        group by thread#) a,
      (select thread# thread, max(sequence#) applied
         from v$log_history
        group by thread#) b
  where a.thread = b.thread
  order by 1;

Add a standby database configuration to cluster database configuration.

SQL> alter database recover managed standby database cancel;

~]$ cat > $ORACLE_HOME/dbs/initcoco1.ora << EOF
spfile='+DATA/COCO/PARAMETERFILE/spfilecoco.ora'
EOF

~]$ cat > $ORACLE_HOME/dbs/initcoco2.ora << EOF
spfile='+DATA/COCO/PARAMETERFILE/spfilecoco.ora'
EOF

SQL> create pfile='/tmp/pfilecoco.ora' from spfile;

SQL> shutdown immediate

SQL> create spfile='+DATA/COCO/PARAMETERFILE/spfilecoco.ora' from pfile='/tmp/pfilecoco.ora';

~]$ srvctl add database -d cocostby -n coco -o /u01/app/oracle/product/11.2.0/db_1 -p +DATA/COCO/spfilecoco.ora -r physical_standby -a DATA,RECO
~]$ srvctl add instance -d cocostby -i coco1 -n oracle11grac03
~]$ srvctl add instance -d cocostby -i coco2 -n oracle11grac04
~]$ srvctl config database -d cocostby
~]$ srvctl start database -d cocostby

SQL> alter database recover managed standby database using current logfile disconnect;

SQL> select inst_id, name, database_role, open_mode from gv$database;

   INST_ID NAME      DATABASE_ROLE    OPEN_MODE
---------- --------- ---------------- --------------------
         1 COCO      PHYSICAL STANDBY READ ONLY WITH APPLY
         2 COCO      PHYSICAL STANDBY READ ONLY WITH APPLY