Advertisements
Skip to content
November 4, 2015 / Shivananda Rao P

Creating RAC Physical Standby database for RAC Primary in 12c

This article focuses on creating a RAC standby database for a RAC primary database. The primary database is on a 2 node cluster and the standby database too will be created on a 2 node cluster. The entire setup is on oracle 12c 12.1.0.1. Also, the article is written with the primary database is using ASM with OMF managed datafiles and the standby, too, will be using OMF. On the other hand, ASM for the standby server is already configured and will not be outlined over here.

 

Environment:

Primary:

DB NAME          :  srprim
DB UNIQUE NAME   :  srprim
Instances        :  srprim1, srprim2
Hostnames        :  ora12c-node1, ora12c-node2

Standby:

DB NAME         :  srprim
DB UNIQUE NAME  :  srpstb
Instances       :  srpstb1, srpstb2
Hostnames       :  ora12cdr1, ora12cdr2

Speaking of the primary database, below is the details of the pluggable databases that are currently plugged into the primary.

 

SQL> select status,instance_name,con_id from v$instance;

STATUS       INSTANCE_NAME        CON_ID
------------ ---------------- ----------
OPEN         srprim1                   0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

 

The configuration here, uses “Flash Recovery Area” on both primary and the standby database. Also, it’s assumed that the primary database is in ARCHIVELOG mode with FORCE LOGGING enabled.
Now, configure the essential parameters on the primary database related to the archival with local and remote destinations.

 

SQL> alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=srprim' sid='*';

System altered.

SQL> alter  system set log_archive_dest_2='service=srpstb valid_for=(online_logfiles,primary_role) db_unique_name=srpstb' sid='*';

System altered.

 

Set the FAL_SERVER parameter with the “NET ALIAS NAME” of the standby database. This parameter will be used only when a switchover occurs and the primary starts behaving in the standby role.

 

SQL> alter system set fal_server='srpstb';

System altered.

 

The PFILE of a primary database looks as below. Make sure the “remote_login_passwordfile” is set to EXCLUSIVE.

 

srprim1.__data_transfer_cache_size=0
srprim2.__data_transfer_cache_size=0
srprim2.__db_cache_size=805306368
srprim1.__db_cache_size=788529152
srprim1.__java_pool_size=16777216
srprim2.__java_pool_size=16777216
srprim1.__large_pool_size=33554432
srprim2.__large_pool_size=33554432
srprim1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
srprim2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
srprim1.__pga_aggregate_target=436207616
srprim2.__pga_aggregate_target=436207616
srprim1.__sga_target=1291845632
srprim2.__sga_target=1291845632
srprim1.__shared_io_pool_size=67108864
srprim2.__shared_io_pool_size=67108864
srprim2.__shared_pool_size=352321536
srprim1.__shared_pool_size=369098752
srprim1.__streams_pool_size=0
srprim2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/srprim/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.1.0.0.0'
*.control_files='+DATA/SRPRIM/CONTROLFILE/current.262.891722993','+FRA/SRPRIM/CONTROLFILE/current.256.893794127'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='srprim'
*.db_recovery_file_dest_size=4194304000
*.db_recovery_file_dest='+FRA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srprimXDB)'
*.enable_pluggable_database=true
*.fal_client='srprim'
*.fal_server='srpstb'
srprim1.instance_number=1
srprim2.instance_number=2
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=srprim'
*.log_archive_dest_2='service=srpstb valid_for=(online_logfiles,primary_role) db_unique_name=srpstb'
*.open_cursors=300
*.pga_aggregate_target=410m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=1230m
srprim2.thread=2
srprim1.thread=1
srprim1.undo_tablespace='UNDOTBS1'
srprim2.undo_tablespace='UNDOTBS2'

 

Make sure that the listener is up and running on both the nodes of the primary database.

 

[oracle@ora12c-node1 ~]$ srvctl status listener Listener
LISTENER is enabled Listener
LISTENER is running on node(s): ora12c-node1,ora12c-node2
[oracle@ora12c-node1 ~]$
[oracle@ora12c-node1 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node ora12c-node1

 

Below is how my listener.ora file looks on the standby database node ora12cdr1.

 

[oracle@ora12cdr1 dbs]$ cat /u01/app/12.1.0.1/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/12.1.0.1/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON

VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF             # line added by Agent

VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET

LISTENER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
    )
  )

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
    (ORACLE_HOME = /u01/app/oracle/product/12.1.0.1/db1)
    (SID_NAME = srpstb1)
  )
)

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

LISTENER_SCAN1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
    )
  )

 

Create TNS entries for the standby database and have them in the TNSNAMES.ora file of both primary and the standby nodes.
SRPRIM, SRPRIM1 and SRPRIM2 are the TNS entries co-related to primary database.
SRPSTB, SRPSTB1 and SRPSTB2 are the TNS entries that co-relate with the standby database.

 

[oracle@ora12cdr1 dbs]$ cat /u01/app/oracle/product/12.1.0.1/db1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.1/db1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SRPRIM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = srprim)
    )
  )

SRPRIM1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-node1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = srprim)
      (instance_name = srprim1)
    )
  )

SRPRIM2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c-node2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = srprim)
      (instance_name = srprim2)
    )
  )

SRPSTB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora12cdr-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = srpstb)
    )
  )

SRPSTB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora12cdr1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = srpstb1)(UR=A)
      (instance_name = srpstb1)
    )
  )

SRPSTB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora12cdr2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = srpstb)
      (instance_name = srpstb2)
    )
  )

 

I have created a PFILE of the primary database parameters at “/u02/initsrprim1.ora” on “primary” node. Copy this over to the standby node “ora12cdr1”.

[oracle@ora12c-node1 ~]$ scp /u02/initsrprim1.ora ora12cdr1:/u03/
The authenticity of host 'ora12cdr1 (192.168.0.121)' can't be established.
RSA key fingerprint is f8:21:ec:7f:b3:68:53:42:12:a8:cf:95:b0:58:3a:5d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'ora12cdr1,192.168.0.121' (RSA) to the list of known hosts.
oracle@ora12cdr1's password:
initsrprim1.ora                                                                                                                      100% 1456     1.4KB/s   00:00
[oracle@ora12c-node1 ~]$

 

Modify the above copied file on the standby node accordingly. Since we are using RMAN active duplicate from RAC to RAC, remove the cluster related parameters on the standby database PFILE.

The cluster related parameters are :

 

#*.cluster_database=true
#srpstb1.instance_number=1
#srpstb2.instance_number=2
#srpstb2.thread=2
#srpstb1.thread=1
#srpstb2.undo_tablespace='UNDOTBS2'

You can see below, that these parameters have been commented out from my standby pfile “initsrpstb1.ora”.
Make sure that “FAL_SERVER” parameter is set to the “NET ALIAS NAME” of the primary database from which the standby will fetch the redo.

Set the “db_file_name_convert” and “log_file_name_convert” parameters accordingly based on the diskgroup names or the “file system locations” that you have. In my case, the disk group names on which the datafiles are stored on primary and standby database is “DATA”, but the redo is stored on “FRA” and “FRA1” on primary and standby respectively.

 

[oracle@ora12cdr1 dbs]$ cat /u03/initsrpstb1.ora
srpstb1.__data_transfer_cache_size=0
srpstb1.__db_cache_size=771751936
srpstb1.__java_pool_size=16777216
srpstb1.__large_pool_size=33554432
srpstb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
srpstb1.__pga_aggregate_target=436207616
srpstb1.__sga_target=1291845632
srpstb1.__shared_io_pool_size=50331648
srpstb1.__shared_pool_size=402653184
srpstb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/srpstb/adump'
*.audit_trail='db'
#*.cluster_database=true
*.compatible='12.1.0.0.0'
*.control_files='+DATA','+FRA1'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='srprim'
*.db_unique_name='srpstb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srpstbXDB)'
*.db_recovery_file_dest_size=4000M
*.db_recovery_file_dest='+FRA1'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=srpstb'
*.log_archive_dest_2='service=srprim valid_for=(online_logfiles,primary_role) db_unique_name=srprim'
*.fal_server='srprim'
*.enable_pluggable_database=true
#srpstb1.instance_number=1
#srpstb2.instance_number=2
*.open_cursors=300
*.pga_aggregate_target=410m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=1230m
#srpstb2.thread=2
#srpstb1.thread=1
srpstb1.undo_tablespace='UNDOTBS1'
#srpstb2.undo_tablespace='UNDOTBS2'
*.log_archive_config='DG_CONFIG=(srprim,srpstb)'
*.log_file_name_convert='+FRA','+FRA1'

 

With 12c, password files will be placed on ASM diskgroup and is a new feature of 12c. To get the password file of the primary database, connect to the ASM instance on the primary node and use the “pwget” command over “asmcmd” utility.

On primary node:

 

ASMCMD> pwget --dbuniquename srprim
+DATA/srprim/orapwsrprim
ASMCMD>

 

Copy the password file from the ASM diskgroup to the local file system (here I have copied it to “/u02/” location on “ora12c-node1”), so that the same can be copied over and used at the standby site.

 

ASMCMD> pwcopy '+DATA/srprim/orapwsrprim' '/u02/orapwsrprim'
copying +DATA/srprim/orapwsrprim -> /u02/orapwsrprim
ASMCMD-9456: password file should be located on an ASM disk group
ASMCMD> exit
[oracle@ora12c-node1 ~]$ ls -lrt /u02/orapwsrprim
-rw-r----- 1 oracle oinstall 7680 Oct 23 17:38 /u02/orapwsrprim

 

Copy the password file to the standby database server “ora12cdr1” and rename it according to the standby instance name.

 

[oracle@ora12c-node1 ~]$ scp /u02/orapwsrprim ora12cdr1:/u01/app/oracle/product/12.1.0.1/db1/dbs/orapwsrpstb1
oracle@ora12cdr1's password:
orapwsrprim                                                                                                                          100% 7680     7.5KB/s   00:00
[oracle@ora12c-node1 ~]$

 

Place the standby instance “orastb1” in nomount stage using the previously created PFILE.

 

[oracle@ora12cdr1 ~]$ . oraenv
ORACLE_SID = [srpstb1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12cdr1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Oct 22 20:00:19 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/u03/initsrpstb1.ora';
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size                  2287960 bytes
Variable Size             452986536 bytes
Database Buffers          822083584 bytes
Redo Buffers                8708096 bytes

 

Connect to SRPRIM as target and “SRPSTB1” as auxiliary through RMAN and initiate the RMAN duplicate.
I’m using the “RMAN active duplicate” method here to create the standby.

 

[oracle@ora12cdr1 dbs]$ rman target sys/oracle@srprim auxiliary sys/oracle@srpstb1

Recovery Manager: Release 12.1.0.1.0 - Production on Fri Oct 23 20:46:09 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SRPRIM (DBID=307664432)
connected to auxiliary database: SRPRIM (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 23-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=31 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '+DATA/srprim/orapwsrprim' auxiliary format
 '/u01/app/oracle/product/12.1.0.1/db1/dbs/orapwsrpstb1'   ;
}
executing Memory Script

Starting backup at 23-OCT-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=78 instance=srprim1 device type=DISK
Finished backup at 23-OCT-15
.
.
.
<output Trimmed>
.
.
.
input datafile copy RECID=26 STAMP=893883014 file name=+DATA/SRPSTB/20E576F1A6FE02E0E0537200A8C01581/DATAFILE/sysaux.260.893882967
datafile 11 switched to datafile copy
input datafile copy RECID=27 STAMP=893883014 file name=+DATA/SRPSTB/20E576F1A6FE02E0E0537200A8C01581/DATAFILE/users.259.893882991
datafile 12 switched to datafile copy
input datafile copy RECID=28 STAMP=893883014 file name=+DATA/SRPSTB/20E576F1A6FE02E0E0537200A8C01581/DATAFILE/example.258.893882993
Finished Duplicate Db at 23-OCT-15

RMAN>

 

Add the related cluster parameters to the standby database.

 

cluster_database=TRUE
srpstb1.undo_tablespace='UNDOTBS1'
srpstb2.undo_tablespace='UNDOTBS2'
srpstb2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=drnode2-vip)(PORT=1521))))'
srpstb1.instance_number=1
srpstb2.instance_number=2
srpstb1.thread=1
srpstb2.thread=2
srpstb1.fal_client='srpstb1'
srpstb2.fal_client='srpstb2'

 

Here is how my PFILE of standby database looks.

 

[oracle@ora12cdr1 dbs]$ cat initsrpstb1.ora
srpstb1.__data_transfer_cache_size=0
srpstb1.__db_cache_size=805306368
srpstb1.__java_pool_size=16777216
srpstb1.__large_pool_size=150994944
srpstb1.__pga_aggregate_target=436207616
srpstb1.__sga_target=1291845632
srpstb1.__shared_io_pool_size=0
srpstb1.__shared_pool_size=285212672
srpstb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/srpstb/adump'
*.audit_trail='DB'
*.background_dump_dest='/u01/app/oracle/diag/rdbms/srpstb/srpstb1/trace'#Deprecate parameter
*.compatible='12.1.0.0.0'
*.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))','((TYPE=EMON)(BROKERS=1))'# connection_brokers default value
*.control_files='+DATA/SRPSTB/CONTROLFILE/current.267.893882827','+FRA1/SRPSTB/CONTROLFILE/current.258.893882827'#Restore Controlfile
*.core_dump_dest='/u01/app/oracle/diag/rdbms/srpstb/srpstb1/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='srprim'
*.db_recovery_file_dest='+FRA1'
*.db_recovery_file_dest_size=4000M
*.db_unique_name='srpstb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srpstbXDB)'
*.enable_pluggable_database=TRUE
*.fal_server='srprim'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=srpstb'
*.log_archive_dest_2='service=srprim valid_for=(online_logfiles,primary_role) db_unique_name=srprim'
*.log_buffer=8343552# log buffer update
*.log_file_name_convert='+FRA','+FRA1'
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=410M
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=300
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=6336K
*.sga_target=1232M
*.skip_unusable_indexes=TRUE
#*.undo_tablespace='UNDBOTBSi1'
*.user_dump_dest='/u01/app/oracle/diag/rdbms/srpstb/srpstb1/trace'#Deprecate parameter
*.cluster_database=TRUE
srpstb1.undo_tablespace='UNDOTBS1'
srpstb2.undo_tablespace='UNDOTBS2'
srpstb1.instance_number=1
srpstb2.instance_number=2
srpstb1.thread=1
srpstb2.thread=2
srpstb1.fal_client='srpstb1'
srpstb2.fal_client='srpstb2'
srpstb1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ora12cdr1-vip)(PORT=1521))))'
srpstb2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ora12cdr2-vip)(PORT=1521))))'
*.remote_listener='ora12cdr-scan:1521'
[oracle@ora12cdr1 dbs]$

 

Connect to the standby instance “srpstb1” and create a global SPFILE to be placed on the diskgroup which will be shared across by all the standby nodes.

 

[oracle@ora12cdr1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Oct 24 10:50:07 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> create spfile='+DATA/srpstb/spfilesrpstb.ora' from pfile;

File created.

 

Create a PFILE on each of the standby nodes to point out to the shared SPFILE.

 

[oracle@ora12cdr1 dbs]$ cat initsrpstb1.ora
spfile='+DATA/srpstb/spfilesrpstb.ora'

[oracle@ora12cdr1 dbs]$ scp initsrpstb1.ora ora12cdr2:/u01/app/oracle/product/12.1.0.1/db1/dbs/initsrpstb2.ora
initsrpstb1.ora                                                                                                                       100%   39     0.0KB/s   00:00
[oracle@ora12cdr1 dbs]$

 

Having done this, we need to move the password file that was copied earlier to the “$ORACLE_HOME/dbs” location of the standby node “ora12cdr1” to ASM.

Once the password file is placed on ASM diskgroup, it would be shared across all the nodes. So, this removes the need for us to copy the password file to each node.

Using the “pwcopy” command through “ASMCMD” utility, copy the password file from “$ORACLE_HOME/dbs” to “+DATA”.

 

[oracle@ora12cdr1 dbs]$ . oraenv
ORACLE_SID = [srpstb1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12cdr1 dbs]$
[oracle@ora12cdr1 dbs]$ asmcmd
ASMCMD> pwcopy '/u01/app/oracle/product/12.1.0.1/db1/dbs/orapwsrpstb1' '+DATA/srpstb/orapwsrpstb'
copying /u01/app/oracle/product/12.1.0.1/db1/dbs/orapwsrpstb1 -> +DATA/srpstb/orapwsrpstb
ASMCMD>

 

Remove the password file that was copied earlier.

 

[oracle@ora12cdr1 dbs]$ pwd
/u01/app/oracle/product/12.1.0.1/db1/dbs
[oracle@ora12cdr1 dbs]$
[oracle@ora12cdr1 dbs]$ rm -rf orapwsrpstb1
[oracle@ora12cdr1 dbs]$

 

Add the standby database “srpstb” and it’s details to the cluster so that it can be managed by clusterware. You can add the relevant options to the database addition accordingly.

 

[oracle@ora12cdr1 dbs]$ srvctl add database -db srpstb -o /u01/app/oracle/product/12.1.0.1/db1 -startoption mount -role physical_standby -pwfile +DATA/srpstb/orapwsrpstb

 

Add the instances “srpstb1” and “srpstb2” to the standby configuration.

 

[oracle@ora12cdr1 dbs]$ srvctl add instance -instance srpstb1 -db srpstb -node ora12cdr1
[oracle@ora12cdr1 dbs]$ srvctl add instance -instance srpstb2 -db srpstb -node ora12cdr2

 

[oracle@ora12cdr1 dbs]$ srvctl status database -db srpstb
Instance srpstb1 is running on node ora12cdr1
Instance srpstb2 is running on node ora12cdr2
[oracle@ora12cdr1 dbs]$

 

Now, it’s time to add the standby redo logs on both primary and the standby databases.

I have my primary database with 2 threads and 2 Online redo log groups per each thread.

 

SQL> select thread#,group#,status,bytes/1024/1024 from v$log;

   THREAD#     GROUP# STATUS           BYTES/1024/1024
---------- ---------- ---------------- ---------------
         1          1 INACTIVE                      50
         1          2 CURRENT                       50
         2          3 INACTIVE                      50
         2          4 CURRENT                       50

 

Based on the oracle documentation, calculate the number of standby redo logs required. This turns out to 6.

(maximum number of logfiles for each thread + 1) * maximum number of threads

(2+1) *3 = 6

So we need to add 6 groups of standby redo logs with 3 groups per thread.

 

SQL> alter database add standby logfile thread 1 size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 size 50M;

Database altered.

SQL> alter database add standby logfile thread 2 size 50M;

Database altered.

SQL> alter database add standby logfile thread 2 size 50M;

Database altered.

SQL> alter database add standby logfile thread 2 size 50M;

Database altered.

 

Query the v$log and v$standby_log views to get the details of the Online Redo logs and the Standby Redo Logs.
We can see that the standby redo log group 5,6 and 7 are associated with thread 1 and standby redo log groups 8,9 and 10 are associated with thread 2.

 

SQL> select thread#,group#,status,bytes/1024/1024 from v$log;

   THREAD#     GROUP# STATUS           BYTES/1024/1024
---------- ---------- ---------------- ---------------
         1          1 INACTIVE                      50
         1          2 CURRENT                       50
         2          3 INACTIVE                      50
         2          4 CURRENT                       50

SQL> select thread#,group#,status,bytes/1024/1024 from v$standby_log;

   THREAD#     GROUP# STATUS     BYTES/1024/1024
---------- ---------- ---------- ---------------
         1          5 UNASSIGNED              50
         1          6 UNASSIGNED              50
         1          7 UNASSIGNED              50
         2          8 UNASSIGNED              50
         2          9 UNASSIGNED              50
         2         10 UNASSIGNED              50

6 rows selected.

 

Connect to the standby database and start the MRP process. Also, verify the recovery status/progress of the standby.

 

[oracle@ora12cdr1 dbs]$ sqlplus sys/oracle@srpstb1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Oct 24 11:15:23 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select inst_id,status,instance_name,con_id from gv$instance;

   INST_ID STATUS       INSTANCE_NAME        CON_ID
---------- ------------ ---------------- ----------
         1 MOUNTED      srpstb1                   0
         2 MOUNTED      srpstb2                   0

SQL> select inst_id,con_id,name,open_mode from gv$pdbs;

   INST_ID     CON_ID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         1          2 PDB$SEED                       MOUNTED
         1          3 PDB1                           MOUNTED
         2          2 PDB$SEED                       MOUNTED
         2          3 PDB1                           MOUNTED

SQL> alter database recover managed standby database disconnect;

Database altered.

 

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
MRP0      APPLYING_LOG         15          2
RFS       IDLE                  0          0

6 rows selected.

 

On the Primary, query the v$archived_log view to get the current archivelog sequence generated.

 

SQL> select thread#,max(sequence#) from v$archived_log  group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             32
         2             23

 

On the standby, compare the above results with the outcome of the below query. This tells me, that the standby is in sync with the primary.

 

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             31
         2             23

COPYRIGHT

© Shivananda Rao P, 2012, 2013, 2014, 2015. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

 

 

 

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: