Soumya's Database Blog : April 2015

Creating a physical standby database on Oracle 12.1.0.2 :-

OS Version:-Red Hat Enterprise Linux Server release 6.4
Oracle version:- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 64bit

System Configuration

primary server:-

edit the following files

vi /etc/hosts

192.168.2.100 server3.soumya.com server3
192.168.2.101 server4.soumya.com server4

:wq

vi /etc/sysconfig/network
HOSTNAME=server3.soumya.com

:wq

vi /etc/sysconfig/network-scripts/ifcfg-eth0

NETMASK=255.255.255.0
IPADDR=192.168.2.100
GATEWAY=192.168.2.1

:wq

Standby Server:-

edit the following files

vi /etc/hosts

192.168.2.100 server3.soumya.com server3
192.168.2.101 server4.soumya.com server4
:wq

vi /etc/sysconfig/network

HOSTNAME=server4.soumya.com

:wq

vi /etc/sysconfig/network-scripts/ifcfg-eth0

NETMASK=255.255.255.0
IPADDR=192.168.2.101
GATEWAY=192.168.2.1

:wq

In both server-- # service network restart
# service NetworkManager restart
# service iptables stop
# chkconfig iptables off
#vi /etc/selinux/config and disable selinux .
After changing inside the file please restart the server.

We have a database called ora12c on primary server and we will install only oracle binaries on
standby server(192.168.2.101).No database should be running in standby server.

Primary database:- ORA12C
Standby database:- ORASTD

Oracle Net configuration:-

Listener.ora configuration for primary database (ORA12C) in primary server (192.168.2.100)

vi $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORA12C)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = ORA12C)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server3.soumya.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle

:wq

Listener.ora configuration for standby database (ORASTD) in standby server (192.168.2.101)
vi $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORASTD)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = ORASTD)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server4.soumya.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle

:wq

On both server(192.168.2.100 & 192.168.2.101), add Oracle Net aliases for both databases and aux alias for RMAN DUPLICATE in
tnsnames.ora:

vi $ORACLE_HOME/network/admin/tnsnames.ora

ORA12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server3.soumya.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA12C)
)
)

ORASTD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server4.soumya.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORASTD)
)
)

:wq

Now we will have to start listener in primary and standby server:-

$ lsnrctl start

Check "tnsping ORA12C" & "tnsping ORASTD" in both server. If output of both command is
coming as "ok" that means oracle net service has been added successfully.

In primary database:-
Put the database in archivelog mode.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 4

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 629145600 bytes
Fixed Size 2927528 bytes
Variable Size 511706200 bytes
Database Buffers 109051904 bytes
Redo Buffers 5459968 bytes
Database mounted.

SQL> alter database archive log;
Database altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4

SQL> alter database open;

In Primary Database:-

Enable force logging The FORCE LOGGING option to ensure that all the changes made in the database
will be captured and available for recovery in the redo logs.

SQL> alter database force logging;
Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

We have to enable remote login by setting remote_login_passwordfile to exclusive in password file
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

Create a password file
$ orapwd file=$ORACLE_HOME/dbs/orapwORA12C password=redhat
$ cd $ORACLE_HOME/dbs/
$ chmod 775 orapwORA12C
$ cp orapwORA12C orapwORASTD

Now transfer the password file into standby server using scp command
$ scp orapwORASTD oracle@192.168.2.101:/u01/app/oracle/product/12.1.0.2/db_1/dbs/

Configure standby redo log file with the same size of redo log file in ORA12C database
SYS@ORA12C> select group#, members , bytes /1024 /1024 from v$log;

GROUP# MEMBERS BYTES/1024/1024
---------- ---------- ---------------
1 1 50
2 1 50
3 1 50

SYS@ORA12C> select group# , member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORA12C/redo03.log
2 /u01/app/oracle/oradata/ORA12C/redo02.log
1 /u01/app/oracle/oradata/ORA12C/redo01.log

Now adding redolog files :-
SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/ORA12C/redos4A.log' size 50M;
SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/ORA12C/redos5A.log' size 50M;
SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/ORA12C/redos6A.log' size 50M;
SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/ORA12C/redos7A.log' size 50M;

SQL> select group# , member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORA12C/redo03.log
2 /u01/app/oracle/oradata/ORA12C/redo02.log
1 /u01/app/oracle/oradata/ORA12C/redo01.log
4 /u01/app/oracle/oradata/ORA12C/redos4A.log
5 /u01/app/oracle/oradata/ORA12C/redos5A.log
6 /u01/app/oracle/oradata/ORA12C/redos6A.log
7 /u01/app/oracle/oradata/ORA12C/redos7A.log

Add following parameter in pfile for primary database.

create pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora' from spfile;

vi /u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora

*.db_name='ORA12C'
*.db_unique_name='ORA12C'
*.log_archive_config='DG_CONFIG=(ORA12C, ORASTD)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA12C'
*.log_archive_dest_2='SERVICE=ORASTD LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORASTD'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.FAL_SERVER=ORASTD
*.FAL_CLIENT=ORA12C
*.db_file_name_convert='/u01/app/oracle/oradata/ORA12C/','/u01/app/oracle/oradata/ORASTD/'
*.log_file_name_convert='/u01/app/oracle/oradata/ORA12C/','/u01/app/oracle/oradata/ORASTD/'
*.standby_file_management=auto

:wq

SQL> shut immediate;

SQL> startup mount pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora';
SQL> create spfile from pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora';

Create a standby controlfile.
sql>alter database create standby controlfile as '/u01/app/oracle/oradata/ORA12C/ORASTD.ctl';

Create parameter file for standby database :

$cp /u01/app/oracle/product/12.1.0.2/db_1/dbs/initORA12C.ora /u01/app/oracle/product/12.1.0.2/db_1/dbs/initORASTD.ora

$ vi /u01/app/oracle/product/12.1.0.2/db_1/dbs/initORASTD.ora

ORASTD.__data_transfer_cache_size=0
ORASTD.__db_cache_size=197132288
ORASTD.__java_pool_size=4194304
ORASTD.__large_pool_size=8388608
ORASTD.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORASTD.__pga_aggregate_target=251658240
ORASTD.__sga_target=377487360
ORASTD.__shared_io_pool_size=8388608
ORASTD.__shared_pool_size=150994944
ORASTD.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORASTD/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/ORASTD/ORASTD.ctl','/u01/app/oracle/fast_recovery_area/ORASTD/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORASTDXDB)'
*.memory_target=600m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_name='ORA12C'
*.db_unique_name='ORASTD'
*.log_archive_config='DG_CONFIG=(ORA12C, ORASTD)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORASTD'
*.log_archive_dest_2='SERVICE=ORASTD LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA12C'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.FAL_SERVER=ORA12C
*.FAL_CLIENT=ORASTD
*.db_file_name_convert='/u01/app/oracle/oradata/ORA12C/','/u01/app/oracle/oradata/ORASTD/'
*.log_file_name_convert='/u01/app/oracle/oradata/ORA12C/','/u01/app/oracle/oradata/ORASTD/'
*.standby_file_management=auto

:wq

In standby server :-
Create the necessary directory structure:-
$ mkdir -p /u01/app/oracle/oradata/ORASTD/
$ mkdir -p /u01/app/oracle/admin/ORASTD/adump
$ mkdir -p /u01/app/oracle/oradata/ORASTD
$ mkdir -p /u01/app/oracle/fast_recovery_area/ORASTD/
$ chmod 775 -Rf /u01

Copy all data files, all redo log files and standby redo log file, standby controlfile, pfile from ORA12C to ORASTD database.

$ cd /u01/app/oracle/oradata/ORA12C
$ ll
-rw-r----- 1 oracle oinstall 10043392 Apr 16 09:57 control01.ctl
-rw-r----- 1 oracle oinstall 10043392 Apr 16 09:47 ORASTD.ctl
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:59 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:41 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:41 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:14 redos4A.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:14 redos5A.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:14 redos6A.log
-rw-r----- 1 oracle oinstall 52429312 Apr 15 12:14 redos7A.log
-rw-r----- 1 oracle oinstall 629153792 Apr 15 12:59 sysaux01.dbf
-rw-r----- 1 oracle oinstall 817897472 Apr 15 12:59 system01.dbf
-rw-r----- 1 oracle oinstall 62922752 Apr 15 12:42 temp01.dbf
-rw-r----- 1 oracle oinstall 62922752 Apr 15 12:59 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Apr 15 12:59 users01.dbf

$ scp *.log oracle@192.168.2.101:/u01/app/oracle/oradata/ORASTD/
oracle@192.168.2.101's password:
redo01.log 100% 50MB 25.0MB/s 00:02
redo02.log 100% 50MB 50.0MB/s 00:00
redo03.log 100% 50MB 25.0MB/s 00:02
redos4A.log 100% 50MB 50.0MB/s 00:01
redos5A.log 100% 50MB 50.0MB/s 00:01
redos6A.log 100% 50MB 50.0MB/s 00:01
redos7A.log 100% 50MB 50.0MB/s 00:01

$ scp *.dbf oracle@192.168.2.101:/u01/app/oracle/oradata/ORASTD/
oracle@192.168.2.101's password:
sysaux01.dbf 100% 600MB 35.3MB/s 00:17
system01.dbf 100% 780MB 39.0MB/s 00:20
temp01.dbf 100% 60MB 60.0MB/s 00:01
undotbs01.dbf 100% 60MB 30.0MB/s 00:02
users01.dbf 100% 5128KB 5.0MB/s 00:00

$ scp ORASTD.ctl oracle@192.168.2.101:/u01/app/oracle/oradata/ORASTD/
oracle@192.168.2.101's password:
ORASTD.ctl 100% 9808KB 9.6MB/s 00:00

Now copy the pfile
$ cd $ORACLE_HOME/dbs
$ scp initORASTD.ora oracle@192.168.2.101:/u01/app/oracle/product/12.1.0.2/db_1/dbs/

In standby server:-
copy the controlfile into 2nd location and rename it as control02.ctl

$ cd /u01/app/oracle/oradata/ORASTD
$ cp ORASTD.ctl /u01/app/oracle/fast_recovery_area/ORASTD/
$ cd /u01/app/oracle/fast_recovery_area/ORASTD/
$ mv ORASTD.ctl control02.ctl

In standby server:-
Setup .bash_profile for standby database
$ vi .bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin
export TMP=/u01/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=server4.soumya.com
export ORACLE_UNQNAME=ORASTD
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export ORACLE_SID=ORASTD
export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/lib64
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

:wq

$ . .bash_profile

In primary database:-
Open the primary database

SQL> alter database open;

In standby database:-

Now start the standby database in mount stage

$ sqlplus / as sysdba

SQL> startup mount pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORASTD.ora';
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2293832 bytes
Variable Size 595595192 bytes
Database Buffers 230686720 bytes
Redo Buffers 2355200 bytes
Database mounted.

SQL> create spfile from pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initORASTD.ora';

Start Physical Standby Database :
SQL> alter database recover managed standby database disconnect from session;

In standby database :
SQL>select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;

Also check the above query in primary database also to confirm the archive log no:-
In Primary database:-
SQL> select sequence#, first_time, next_time,dest_id, thread# , standby_dest, applied from v$archived_log order by sequence#;

In primary database :
sql>alter system switch logfile;

Note: if archive logfile is coming to prodcws database, that means configuration is correct till now.

Now for real time apply:-
Steps :

# In Standby Database

SQL> alter database recover managed standby database cancel;

SQL> alter database open;

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

select open_mode from v$database; (Output : read only with apply)

After commit on primary, it will directly apply on standby database.

+++++ END +++++

Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.