OS: Oracle Linux 7.4
DB: 11.2.0.4
Important Notes: First of all, we have tow know that we can't use scan name for active duplication from RAC and we can only use one of the nodes - no parallel.
On Standby side (auxiliary) we will use listener from database home not grid home. If we will use grid home then duplication will fail.
Step 1. Enable database in force logging mode if not - Primary
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
Step 2. Enable Archiving - Primary
srvctl stop database -d avisdb
srvctl start database -d avisdb -o mount
SQL>alter database archivelog;
SQL> alter database open;
(SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;)
Step 3. Add Standby Logfiles for each thread - Primary Database
alter database add standby logfile thread 1
group 7 ('+DATA','+FRA') SIZE 300M,
group 8 ('+DATA','+FRA') SIZE 300M,
group 9 ('+DATA','+FRA') SIZE 300M,
group 10 ('+DATA','+FRA') SIZE 300M;
alter database add standby logfile thread 2
group 11 ('+DATA','+FRA') SIZE 300M,
group 12 ('+DATA','+FRA') SIZE 300M,
group 13 ('+DATA','+FRA') SIZE 300M,
group 14 ('+DATA','+FRA') SIZE 300M;
Step 4. Execute followings in SQL Plus - Primary
alter system set db_unique_name=avisdb scope=spfile;
alter system set log_archive_config='DG_CONFIG=(avisdb,stby)' scope=both;
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=avisdb' scope=both;
alter system set log_archive_dest_2='SERVICE=stby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby' scope=both;
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
alter system set FAL_SERVER=stby scope=both;
alter system set FAL_CLIENT=avisdb scope=both;
alter system set DB_FILE_NAME_CONVERT='+DATA/stby/datafile','+DATA/avisdb/datafile' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='+DATA','+DATA','+FRA','+FRA' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
Step 5. Create standby controlfile and copy to Standby Database location - Primary DATABASE
alter database create standby controlfile as '/home/oracle/stby.ctl';
[oracle@rac1 ~]$ scp stby.ctl oracle@dbstby:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
oracle@dbstby's password:
stby.ctl 100% 33MB 86.6MB/s 00:00
Step 6. Copy password file to standbay ../dbs and change name - Primary DATABASE
[oracle@rac1 ~]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwavisdb1 oracle@dbstby:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
oracle@dbstby's password:
orapwavisdb1
mv orapwavisdb1 orapwstby
Step 7. Update listener.ora in Primary - Primary Database
/u01/app/11.2.0/grid/network/admin
[oracle@rac1 admin]$ cat listener.ora
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
# listener.ora.rac1 Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora.rac1
# Generated by Oracle configuration tools.
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3 = ON
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2 = ON
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_DUP = ON
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
LISTENER_SCAN3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN3))
)
)
LISTENER_SCAN2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN2))
)
)
LISTENER_SCAN1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = avisdb1)
)
)
Step 8. Update tnsnames.ora file in Primary -Primary Database
[oracle@db admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
avisdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =rac1)(PORT = 1521)) #HOST=rac-scan or rac1
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = avisdb)
(UR=A)
)
)
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbstby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
(UR=A)
)
)
Step 9. Restart LISTENER - Primary Database
[oracle@rac1 admin]$ srvctl stop listener
[oracle@rac1 admin]$ srvctl start listener
Step 10. Add Standby Database parameters to system file for oracle which is used for ora environment. - Standby Database
[oracle@duplicate admin]$ vi /etc/oratab
stby:/u01/app/oracle/product/12.2.0/dbhome_1:N
Step 11. Update tnsnames.ora - Standby Database
oracle@dbstby admin]$ cat tnsnames.ora
avisdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =rac1)(PORT = 1521)) #HOST=rac-scan or rac1
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = avisdb)
(UR=A)
)
)
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbstby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
(UR=A)
)
)
Step 12. Stop listener from GRID_HOME. Create new listener in ORACLE_HOME and start it - Standby Database
srvctl stop listener
srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is not running
[oracle@dbstby admin]$ vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbstby)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=stby)
(GLOBAL_DBNAME=stby)
)
)
export PATH=$PATH:$HOME/.local/bin:$HOME/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin
lsnrctl start
lsnrctl status
Step 13. Create new directories for new database data files and control files as - Standby Database
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/admin/stby/adump
Step 14. Create parameter file initstbyad.ora - Standby Database
[oracle@duplicate dbs]$ vi initstby.ora
DB_NAME=avisdb
Step 15. Set parameters - Standby Database
[oracle@dbstby dbs]$ sqlplus / as sysdba
startup nomount;
create spfile from pfile;
startup nomount force;
alter system set control_files='+DATA','+FRA' scope=spfile;
alter system set db_unique_name=stby scope=spfile;
alter system set DB_FILE_NAME_CONVERT='+DATA/avisdb/datafile','+DATA/stby/datafile' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='+DATA','+DATA','+FRA','+FRA' scope=spfile;
alter system set log_archive_max_processes=10 scope=spfile;
alter system set fal_client='stby' scope=both;
alter system set fal_server='avisdb' scope=both;
alter system set standby_file_management='AUTO';
alter system set log_archive_config='DG_CONFIG=(avisdb,stby)' scope=both;
alter system set db_recovery_file_dest_size=2000G scope=spfile;
alter system set db_recovery_file_dest='+FRA' scope=spfile;
startup nomount force;
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stby' scope=both;
alter system set log_archive_dest_2='SERVICE=avisdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=avisdb' scope=both;
Change to RMAN
[oracle@duplicate stbyad]$ rman target=/
connected to target database: avisdb (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/stby.ctl'; (This is the controlfile created on Step 7 from CSD.txt)
SQL> alter system set compatible='11.2.0.4.0' scope=spfile;
(SQL> startup nomount force;
SQL> alter database mount; )
or
SQL> startup mount force;
SQL> startup nomount force;
Step 16. Duplication for standby - Primary Database
[oracle@rac1 ~]$ rman target=sys/xxxxxx@avisdb auxiliary=sys/xxxxxx@stby
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 2 09:10:09 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: AVISDB (DBID=676626585)
connected to auxiliary database: AVISDB (not mounted)
RMAN> spool log to '/home/oracle/output.log';
RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate channel prmy5 type disk;
7> allocate auxiliary channel stby1 type disk;
8> duplicate target database for standby from active database
9> dorecover
10> nofilenamecheck;
11> }
Step 17. Update tnsnames.ora in each Node to apply logs.
Change node name to scan name. And add new service name to each RAC node for standby to apply logs.
Step 18. Update listener.ora in Node1 as remove
(SID_LIST =
(SID_DESC =
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = avisdb2)
)
)
Step 19. Issue the following command to start Redo Apply
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Step 20. Then drop tempfile metada where it defined in nodes other than duplicated (here Node2 tempfile).
alter database tempfile '+DATA/avisdb/tempfile/temp2' drop;
Step 21. 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