Creating a physical standby database on Oracle 12.1.0.2

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.



No comments:

Post a Comment