Steps for creating Single instance standby database from RAC primary database :-
- Change the archive log mode :
$ sqlplus / as sysdba SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination +DG01 Oldest online log sequence 299300 Next log sequence to archive 299305 Current log sequence 299305
2. Enable force logging mode:
SQL> select force_logging from v$database; FORCE_LOGGING --------------------------------------- NO SQL> alter database force logging; Database altered. SQL> select force_logging from v$database; FORCE_LOGGING --------------------------------------- YES
3. Parameter Configuration setup:
SQL> alter system set log_archive_config='DG_CONFIG=(prod,proddr)' SCOPE=both sid='*'; System altered. SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/prod/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod' SCOPE=both sid='*'; System altered. SQL> alter system set log_archive_dest_2='SERVICE=proddr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddr' SCOPE=both sid='*'; SQL> alter system set fal_server=prod SCOPE=both sid='*'; System altered. SQL> alter system set fal_client=proddr SCOPE=both sid='*'; System altered. SQL> alter system set standby_file_management=auto SCOPE=both sid='*'; System altered. SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive scope=spfile; System altered.
4. Standby Listener Configuration:
[oracle@proddr01 ]$ export ORACLE_SID=prod [oracle@proddr01 ]$ export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1 [oracle@proddr01 admin]$ cd $ORACLE_HOME/network/admin [oracle@proddr01 admin]$ cat listener.ora # listener.ora Network Configuration File: /oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1) (SID_NAME = prod ) ) ) LISTENER_PRODDR= (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = proddr01)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle
5. TNS Connection string Configuration :
Standby and primary tnsnames.ora entry should be available in both nodes:
[oracle@proddr01 admin]$ cd $ORACLE_HOME/network/admin [oracle@proddr01 admin]$ cat tnsnames.ora PROD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = prod1)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = prod1) ) ) PRODDR = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = proddr01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = prod) ) )
6. Create respective directories in Standby Server:
[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/ctrl [oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/data [oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/logs [oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/arch [oracle@proddr01 admin]$ mkdir /oracle/app/oracle/admin/proddr/adump
7. Start Standby listener :
[oracle@proddr01 admin] $lsnrctl start LISTENER_PRODDR LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JAN-2019 14:05:49 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting listener to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=proddr01.localdomain.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER_PRODDR Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 03-DEC-2018 14:09:08 Uptime 55 days 23 hr. 56 min. 40 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/app/oracle/product/12.1.0/db_1/network/admin/listener.ora Listener Log File /oracle/app/oracle/diag/tnslsnr/proddr01/listener_proddr/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=proddr01)(PORT=1521))) Services Summary... Service "proddr" has 1 instance(s). Instance "proddr", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
8. Copying password & parameter file to standby server:
- After copying pfile,only keep the parameter entry in PFILE:
db_name
[oracle@proddr01 ]$ cd $ORACLE_HOME/dbs [oracle@prod1 dbs]$ scp initprod.ora orapwprod oracle@proddr01:/oracle/app/oracle/product/12.1.0/dbhome_1/dbs oracle@proddr01's password: initprod.ora 100% 1536 1.5KB/s 00:00 orapwprod 100% 1536 1.5KB/s 00:00 [oracle@proddr01 dbs]$ cat initprod.ora db_name='prod'
9. Check connectivity between primary and standby side :
[oracle@proddr01 ]$ tnsping prod [In boths the nodes] [oracle@proddr01 ]$ tnsping proddr [In boths the nodes]
10. Standby Database Creation :
Startup in nomount stage :
[oracle@proddr01 ]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 29 01:12:25 2019 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 217157632 bytes Fixed Size 2211928 bytes Variable Size 159387560 bytes Database Buffers 50331648 bytes Redo Buffers 5226496 bytes
11. Connect RMAN to create standby database,
Set cluster_database is FALSE.
[oracle@proddr01 ]$ rman target sys/****@prod auxiliary sys/****@proddr
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jan 27 16:15:10 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=1459429229)
connected to auxiliary database: PROD (not mounted)
RMAN> run
{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'prod','proddr'
set db_file_name_convert='+DG01/prod/datafile','/oradata1/proddr/data'
set db_unique_name='proddr'
set cluster_database='false'
set log_file_name_convert='+DG01/prod/onlinelog','/oradata1/proddr/logs'
set control_files='/oracle/app/oracle/oradata/proddr/ctrl/control.ctl'
set fal_client='proddr'
set fal_server='prod'
set audit_file_dest='/oracle/app/oracle/admin/proddr/adump'
set log_archive_config='dg_config=(proddr,prod)'
set log_archive_dest_1='location=location=/oradata1/prod/arch'
set log_archive_dest_2='service=prod async valid_for=(online_logfiles,primary_role) db_unique_name=prod'
set sga_target='50GB'
set sga_max_size='50GB'
set undo_tablespace='UNDOTBS1'
nofilenamecheck;
}
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=42 device type=DISK
allocated channel: prmy2
channel prmy2: SID=36 device type=DISK
allocated channel: prmy3
channel prmy3 : SID=45 device type=DISK
allocated channel: prmy4
channel prmy4 : SID=45 device type=DISK
allocated channel: stby
channel stby: SID=20 device type=DISK
Starting Duplicate Db at 28-JAN-19
.
.
.
.
.
Finished Duplicate Db at 28-JAN-19
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN>
12. Enable Recovery Manager in standby side:
[oracle@proddr01 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Mon Jan 28 10:36:39 2019 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter database recover managed standby database disconnect from session; Database altered.
13. Check Standby SYNC Verification:
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ----------- 1 299314 299314 0 2 149803 149803 0
Catch Me On:- Hariprasath Rajaram Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba
