In this post we will install standalone standby database for 2-node RAC. Assume that we have already configured RAC, if not see the instructions here.
RAC description:
Instance name: orcl1
Hostname : node1
Instance name: orcl2
Hostname : node2
Standby description:
Instance name will be: stbydb
Hostname : stbynode
All of these instances are using ASM.
So let’s start…
On primary database:
0. Add entries in hosts file.
On standby :
127.0.0.1 localhost
192.168.34.150 node1
192.168.34.151 node2
10.10.2.50 node1-priv
10.10.2.51 node2-priv
192.168.34.154 node1-vip
192.168.34.155 node2-vip
192.168.34.160 rac-scan
192.168.34.156 stbynode
On node1,node2:
127.0.0.1 localhost.localdomain localhost
192.168.34.150 node1
192.168.34.151 node2
10.10.2.50 node1-priv
10.10.2.51 node2-priv
192.168.34.154 node1-vip
192.168.34.155 node2-vip
192.168.34.160 rac-scan
192.168.34.156 stbynode
1. Enable archiving.
On node1:
[oracle@node1 ~]$ export ORACLE_SID=orcl1
sqlplus / as sysdba
SQL> select log_mode from v$database;
LOG_MODE
————
NOARCHIVELOG
SQL> shutdown immediate;
SQL> startup mount;
On node2:
export ORACLE_SID=orcl2
sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup mount;
Note that: In RAC environment to take database to archivelog mode , you must take all instances into mount state.
On node1:
SQL> alter database archivelog;
SQL> alter database open;
On node2:
SQL> alter database open;
On any node:
SQL> select log_mode from v$database;
LOG_MODE
————
ARCHIVELOG
2. Enable force logging.
On node1:
SQL> select force_logging from v$database;
FOR
—
NO
SQL> alter database force logging;
SQL> select force_logging from v$database;
FOR
—
YES
3. Modify the following parameters:
On node1:
SQL> show parameter pfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string +DATA/orcl/spfileorcl.ora
SQL> create pfile from spfile;
Edit the following parameters into initorcl1.ora:
log_file_name_convert=’+DATA/stbydb’,’+DATA/orcl’
db_file_name_convert=’+DATA/stbydb’,’+DATA/orcl’
fal_client=’orcl1′
fal_client=’orcl2′
fal_server=’stbydb’
log_archive_config=’dg_config=(orcl,stbydb)’
log_archive_dest_state_1=’enable’
log_archive_dest_state_2=’ENABLE’
log_archive_dest_state_3=’enable’
log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl’
log_archive_dest_2=’SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb’
log_archive_dest_3=’LOCATION=+DATA/orcl/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=orcl’
log_archive_max_processes=7
log_archive_min_succeed_dest=2
remote_login_passwordfile=’EXCLUSIVE’
standby_file_management=’auto’
sec_case_sensitive_logon=FALSE
My initorcl1.ora looks like this:
orcl1.__db_cache_size=503316480
orcl2.__db_cache_size=486539264
orcl1.__java_pool_size=16777216
orcl2.__java_pool_size=16777216
orcl1.__large_pool_size=16777216
orcl2.__large_pool_size=16777216
orcl1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
orcl2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
orcl1.__pga_aggregate_target=587202560
orcl2.__pga_aggregate_target=587202560
orcl1.__sga_target=872415232
orcl2.__sga_target=872415232
orcl1.__shared_io_pool_size=0
orcl2.__shared_io_pool_size=0
orcl1.__shared_pool_size=318767104
orcl2.__shared_pool_size=335544320
orcl1.__streams_pool_size=0
orcl2.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/orcl/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/orcl/controlfile/current.260.793127259′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_name=’orcl’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’
orcl2.instance_number=2
orcl1.instance_number=1
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_listener=’rac-scan:1521′
*.remote_login_passwordfile=’exclusive’
orcl1.thread=1
orcl2.thread=2
orcl2.undo_tablespace=’UNDOTBS1′
orcl1.undo_tablespace=’UNDOTBS2′
log_file_name_convert=’+DATA/stbydb’,’+DATA/orcl’
db_file_name_convert=’+DATA/stbydb’,’+DATA/orcl’
fal_client=’orcl1′
fal_client=’orcl2′
fal_server=’stbydb’
log_archive_config=’dg_config=(orcl,stbydb)’
log_archive_dest_state_1=’enable’
log_archive_dest_state_2=’ENABLE’
log_archive_dest_state_3=’enable’
log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl’
log_archive_dest_2=’SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb’
log_archive_dest_3=’LOCATION=+DATA/orcl/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=orcl’
log_archive_max_processes=7
log_archive_min_succeed_dest=2
remote_login_passwordfile=’EXCLUSIVE’
standby_file_management=’auto’
*.sec_case_sensitive_logon=FALSE
SQL> shutdown immediate;
SQL> startup nomount pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl1.ora’
On node2:
[oracle@node2 ~]$ export ORACLE_SID=orcl2
[oracle@node2 ~]$ sqlplus / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
On node1:
SQL> create spfile=’+DATA/ORCL/spfileorcl.ora’ from pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl1.ora’;
Edit initorcl1.ora file and write just the following entry:
SPFILE=’+DATA/ORCL/spfileorcl.ora’
And start the instance.
SQL> shutdown immediate;
SQL> startup
On node2:
If you try to open node2, it will stuck because it will try to send archivelog to standby location, which is not completely configured yet. So let node2 to be in down state.
3. Create same password files for all instances(passwords must be the same ).
On node1:
[oracle@node1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworcl1 password=oracle force=y entries=5 ignorecase=y
On node2:
[oracle@node2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworcl2 password=oracle force=y entries=5 ignorecase=y
On stbynode:
[oracle@stbynode ~]$ orapwd file=$ORACLE_HOME/dbs/orapwstbydb password=oracle entries=5 force=y ignorecase=y
4. Create standby logfiles on primary.
On node1:
SQL> sqlplus / as sysdba
SQL> select max(length(member)) from v$logfile;
MAX(LENGTH(MEMBER))
——————-
42
SQL> col member for a42
SQL> select group#,member,type from v$logfile order by 1;
GROUP# MEMBER TYPE
———- —————————————— ——-
1 +DATA/orcl/onlinelog/group_1.261.793127267 ONLINE
2 +DATA/orcl/onlinelog/group_2.262.793127267 ONLINE
3 +DATA/orcl/onlinelog/group_3.266.793128605 ONLINE
4 +DATA/orcl/onlinelog/group_4.267.793128605 ONLINE
SQL> alter database add standby logfile thread 1;
SQL> alter database add standby logfile thread 2;
SQL> select group#,member,type from v$logfile order by 1;
GROUP# MEMBER TYPE
———- —————————————— ——-
1 +DATA/orcl/onlinelog/group_1.261.793127267 ONLINE
2 +DATA/orcl/onlinelog/group_2.262.793127267 ONLINE
3 +DATA/orcl/onlinelog/group_3.266.793128605 ONLINE
4 +DATA/orcl/onlinelog/group_4.267.793128605 ONLINE
5 +DATA/orcl/onlinelog/group_5.273.793983817 STANDBY
5 +DATA/orcl/onlinelog/group_5.274.793983819 STANDBY
6 +DATA/orcl/onlinelog/group_6.271.793983823 STANDBY
6 +DATA/orcl/onlinelog/group_6.272.793983823 STANDBY
5. Create parameter file for standby database.
SQL> create pfile=’$ORACLE_HOME/dbs/initstbydb.ora’ from spfile;
Edit required parameters. Delete red entries. Green fields are corrected entries:
orcl2.__db_cache_size=486539264
orcl1.__db_cache_size=503316480
orcl2.__java_pool_size=16777216
orcl1.__java_pool_size=16777216
orcl2.__large_pool_size=16777216
orcl1.__large_pool_size=16777216
orcl1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
orcl2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
orcl2.__pga_aggregate_target=587202560
orcl1.__pga_aggregate_target=587202560
orcl2.__sga_target=872415232
orcl1.__sga_target=872415232
orcl2.__shared_io_pool_size=0
orcl1.__shared_io_pool_size=0
orcl2.__shared_pool_size=335544320
orcl1.__shared_pool_size=318767104
orcl2.__streams_pool_size=0
orcl1.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/stbydb/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/stbydb/controlfile/control01.ctl’
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_file_name_convert=‘+DATA/orcl’,’+DATA/stbydb’
*.db_name=’orcl’
*.db_unique_name=’stbydb’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbydbXDB)’
*.fal_client=’stbydb‘
*.fal_server=’orcl‘
orcl2.instance_number=2
orcl1.instance_number=1
*.log_archive_config=’dg_config=(orcl,stbydb)’
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbydb‘
*.log_archive_dest_2=’SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl‘
*.log_archive_dest_3=’LOCATION=+DATA/stbydb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stbydb‘
*.log_archive_dest_state_1=’enable’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_dest_state_3=’enable’
*.log_archive_format=’%t_%s_%r.dbf’
*.log_archive_max_processes=7
*.log_archive_min_succeed_dest=2
*.log_file_name_convert=’+DATA/orcl‘,’+DATA/stbydb‘
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_listener=’rac-scan:1521′
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’auto’
orcl1.thread=1
orcl2.thread=2
orcl2.undo_tablespace=’UNDOTBS1′
orcl1.undo_tablespace=’UNDOTBS2′
sec_case_sensitive_logon=FALSE
—initstbydb.ora
db_cache_size=503316480
java_pool_size=16777216
large_pool_size=16777216
pga_aggregate_target=587202560
sga_target=872415232
shared_pool_size=318767104
streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/stbydb/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/stbydb/controlfile/control01.ctl’
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_file_name_convert=’+DATA/orcl’,’+DATA/stbydb’
*.db_name=’orcl’
*.db_unique_name=’stbydb’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbydbXDB)’
*.fal_client=’stbydb’
*.fal_server=’orcl’
*.log_archive_config=’dg_config=(stbydb,orcl)’
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbydb’
*.log_archive_dest_2=’SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl’
*.log_archive_dest_3=’LOCATION=+DATA/stbydb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stbydb’
*.log_archive_dest_state_1=’enable’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_dest_state_3=’enable’
*.log_archive_format=’%t_%s_%r.dbf’
*.log_archive_max_processes=7
*.log_archive_min_succeed_dest=2
*.log_file_name_convert=’+DATA/orcl’,’+DATA/stbydb’
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_listener=’rac-scan:1521′
*.remote_login_passwordfile=’EXCLUSIVE’
STANDBY_FILE_MANAGEMENT=’auto’
sec_case_sensitive_logon=FALSE
Copy parameter file to standby.
[oracle@node1 dbs]$ scp initstbydb.ora oracle@stbynode:$ORACLE_HOME/dbs
The authenticity of host ‘stbynode (192.168.34.156)’ can’t be established.
RSA key fingerprint is 73:fa:1f:a0:04:41:68:98:30:25:28:55:59:9c:07:9b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘stbynode,192.168.34.156’ (RSA) to the list of known hosts.
oracle@stbynode’s password:
initstbydb.ora 100% 1479 1.4KB/s 00:00
6. Create required directories on primary and standby databases.
On node1:
mkdir -p /u01/app/oracle/backup
On stbynode
mkdir -p /u01/app/oracle/admin/stbydb/adump
mkdir -p /u01/app/oracle/backup
su – grid
[grid@stbynode ~]$ asmcmd
ASMCMD> ls
CRS/
DATA/
ASMCMD> cd data
ASMCMD> ls
ASMCMD> mkdir stbydb
ASMCMD> cd stbydb
ASMCMD> mkdir ARCHIVELOG CONTROLFILE DATAFILE ONLINELOG PARAMETERFILE STANDBYLOG TEMPFILE
7. Add tns entries in tnsnames.ora file and listener entry in listener.ora file:
–tnsnames.ora
On node1, node2 and stbynode:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
stbydb=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stbynode)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stbydb)
)
)
On node1, node2 and stbynode:
[oracle@node2 dbs]$ tnsping orcl
TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 14-SEP-2012 17:04:13
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (20 msec)
[oracle@node2 dbs]$ tnsping stbydb
TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 14-SEP-2012 17:04:20
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stbynode)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stbydb)))
OK (20 msec)
8. Add entry in oratab.
On stbynode:
vi /etc/oratab
+ASM:/u01/app/11.2.0/grid:N
stbydb:/u01/app/oracle/product/11.2.0/db_1:N
9. Making backups on primary(node1) and copy them to standby.
On node1:
export ORACLE_SID=orcl1
rman target /
RMAN> configure channel device type disk format ‘/u01/app/oracle/backup/%U’;
RMAN> backup database plus archivelog;
RMAN> backup current controlfile for standby;
RMAN> backup archivelog all;
[oracle@node1 ~]$ scp /u01/app/oracle/backup/* oracle@stbynode:/u01/app/oracle/backup/
10. Duplicate database.
On stbynode:
[oracle@stbynode ~]$ export ORACLE_SID=stbydb
[oracle@stbynode ~]$ sqlplus / as sysdba
SQL> startup nomount;
[oracle@stbynode ~]$ export ORACLE_SID=stbydb
[oracle@stbynode ~]$ rman target sys/oracle@orcl auxiliary /
Recovery Manager: Release 11.2.0.1.0 – Production on Fri Sep 14 19:50:02 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1320907995)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby nofilenamecheck;
[oracle@stbynode ~]$ export ORACLE_SID=stbydb
[oracle@stbynode ~]$ sqlplus / as sysdba
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
11. Startup all.
On node2:
SQL> startup;
On stbynode:
alter database open;
12. Test.
On node1:
SQL> create table b(b number);
SQL> insert into b values(1);
SQL> commit;
SQL> alter system switch logfile;
On node2:
SQL> alter system switch logfile;
On stbynode:
SQL> select * from b;
B
———-
1
On node1:
ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
SELECT sequence#, first_time, next_time
FROM v$archived_log
ORDER BY sequence#;
ALTER SYSTEM SWITCH LOGFILE;
SELECT sequence#, first_time, next_time
FROM v$archived_log
ORDER BY sequence#;
On node2:
SQL> alter system switch logfile;
On stbynode:
ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;
DBA Knowledge Base 