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 to 2018. 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.
Shivananda Rao 
Leave a Reply