Pages

Tuesday, July 21, 2020

Dataguard Broker Configuration In Oracle 12c

Dataguard Broker Configuration In Oracle 12c


Primary side:

oracle@mqm-testdb1:~$ export ORACLE_SID=QPROD
oracle@mqm-testdb1:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 12:30:27 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.


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

Check the dgbroker config files:

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/12.1.0.2/db_1/dbs/dr1QPROD.dat
dg_broker_config_file2               string      /u01/app/oracle/product/12.1.0.2/db_1/dbs/dr2QPROD.dat

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb1:~$

DGMGRL> exit
oracle@mqm-testdb1:~$ ps -ef|grep pmon
    grid 12699     1   0   Oct 22 ?          35:04 asm_pmon_+ASM1
    grid 23378     1   0   Oct 22 ?          28:37 mdb_pmon_-MGMTDB
  oracle 28481     1   0   Jan 07 ?          23:02 ora_pmon_OGGSRC1
  oracle 14556     1   0   Feb 18 ?           9:15 ora_pmon_QPROD1
  oracle 24956 11452   0 12:34:49 pts/13      0:00 grep pmon

De-activate the log shipping:

oracle@mqm-testdb1:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:10:15 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='defer' SCOPE=BOTH sid='*';
System altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
QPROD1

SQL> select password from sys.user$ where name like 'SYS';

PASSWORD
--------------------------------------------------------------------------------
27889DA827C33694

Check the passwordfile parameters:

SQL> show parameter remote

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string      mqm-testscan.local:1521
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE
result_cache_remote_expiration       integer     0
SQL>

Change the sys password from sql level:

SQL> alter user sys identified by sys_1234;
User altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

Check the passwordfile status:

oracle@mqm-testdb1:~$ srvctl config database -d QPROD
Database unique name: QPROD
Database name: QPROD
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA/QPROD/spfilerac2.ora
Password file: +DATA/QPROD/PASSWORD/pwdprQPROD/
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,REDO
Mount point paths:
Services: QUATRAC,ANSQ
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: QPROD1,QPROD2
Configured nodes: mqm-testdb1,mqm-testdb2
Database is administrator managed
oracle@mqm-testdb1:~$
oracle@mqm-testdb1:~$
oracle@mqm-testdb1:~$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:19:37 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Create the passwordfile using orapwd utility:

oracle@mqm-testdb1:~$ orapwd file='+DATA/QPROD/PASSWORD/pwdQPROD' dbuniquename='QPROD' password=sys_1234 entries=10

Add the passwordfile using srvctl:

oracle@mqm-testdb1:~$ srvctl modify database -d QPROD -pwfile '+DATA/QPROD/PASSWORD/pwdQPROD'

oracle@mqm-testdb1:~$ srvctl config database -d QPROD
Database unique name: QPROD
Database name: QPROD
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA/QPROD/spfilerac2.ora
Password file: +DATA/QPROD/PASSWORD/pwdQPROD
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,REDO
Mount point paths:
Services: QUATRAC,ANSQ
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: QPROD1,QPROD2
Configured nodes: mqm-testdb1,mqm-testdb2
Database is administrator managed

Test the new passwordfile:

oracle@mqm-testdb1:~$ sqlplus sys/sys_1234@QPROD as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:25:36 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> exit

Activate the log shipping:

oracle@mqm-testdb1:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:40:07 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='enable' SCOPE=BOTH sid='*';
System altered.

Check the config file status:

oracle@mqm-testdb1:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:49:21 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/12.1.0.2/db_1/dbs/dr1QPROD.dat
dg_broker_config_file2               string      /u01/app/oracle/product/12.1.0.2/db_1/dbs/dr2QPROD.dat

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
QPROD1

Move the config files to ASM:

oracle@mqm-testdb1:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:49:21 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> alter system set dg_broker_start=FALSE scope=both sid='*';
System altered.

SQL> alter system set dg_broker_config_file1='+DATA/QPROD/DGBROKERCONFIGFILE/dr1QPROD.dat' scope=both sid='*';
System altered.

SQL> alter system set dg_broker_config_file2='+DATA/QPROD/DGBROKERCONFIGFILE/dr2QPROD.dat' scope=both sid='*';
System altered.

SQL> alter system set dg_broker_start=TRUE scope=both sid='*';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='' scope=both;
System altered.

Create the configuration:

oracle@mqm-testdb1:~$ dgmgrl
DGMGRL for Solaris: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys_1234@QPROD
Connected as SYSDBA.

DGMGRL> CREATE CONFIGURATION UATRAC_DG AS PRIMARY DATABASE IS QPROD CONNECT IDENTIFIER IS QPROD;
Configuration "UATRAC_DG" created with primary database "QPROD"

DGMGRL> ADD DATABASE QPRODN AS CONNECT IDENTIFIER IS QPRODN MAINTAINED AS PHYSICAL;
Database "QPRODN" added

Enable the configuration:

DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> show configuration;

Configuration - UATRAC_DG

  Protection Mode: MaxPerformance
  Members:
  QPROD  - Primary database
    QPRODn - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 6 seconds ago)


Check the status:

oracle@mqm-testdb1:~$ dgmgrl
DGMGRL for Solaris: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys_1234@QPROD
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - UATRAC_DG

  Protection Mode: MaxPerformance
  Members:
  QPROD  - Primary database
    QPRODn - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 47 seconds ago)

DGMGRL> show database QPROD

Database - QPROD

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    QPROD1
    QPROD2

Database Status:
SUCCESS

DGMGRL> show database QPRODN

Database - QPRODn

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 66.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    QPRODN1 (apply instance)
    QPRODN2

Database Status:
SUCCESS


Stop and Start Log Shipping for an Oracle Standby Database

Stop and Start Log Shipping for an Oracle Standby Database



DEACTIVATE THE LOG SHIPPING:

SQL> alter system set log_archive_dest_state_2=defer scope=both; (If RAC  sid = '*';)
System altered.

SQL> show parameter log_archive_dest_state_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
log_archive_dest_state_2             string      DEFER

SQL> select max(sequence#) from v$log_history;
         54276

SQL> alter system switch logfile;

SQL> select max(sequence#) from v$log_history;
         24277


ACTIVATE THE LOG SHIPPING:

SQL> alter system set log_archive_dest_state_2=enable scope=both; (If RAC  sid = '*';)

SQL> show parameter log_archive_dest_state_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
log_archive_dest_state_2             string      ENABLE

SQL> select max(sequence#) from v$log_history;
         54279

SQL> alter system switch logfile;

SQL> select max(sequence#) from v$log_history;
         54280

Standby Creation Steps Using RMAN



Take the primary database backup using rman:

With compression:

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL format '/u02/rmanbkp/full%u' ;
backup tag ORCL_ARCHIVE format '/u02/rmanbkp/%d_%T_%s_%p_ARCHIVE' archivelog all;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

run
{
backup current controlfile for standby format '/u02/rmanbkp/standby.ctl';
}


Without compression:

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
backup database plus archivelog format '/u02/rmanbkp/rmanlog';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

run
{
backup current controlfile for standby format '/u02/rmanbkp/standby.ctl';
}


Once rman backup succesfully completed, transfer the backups from primary to standby:

oracle@mqm-testdb1:/u02/rmanbkp$ scp * oracle@10.40.135.23:/u02/rmanbkp_BKP08032020/


Note down the archive log sequence number.

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

SQL> alter system switch logfile;
System altered.


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7619
Next log sequence to archive   7627
Current log sequence           7627
SQL> exit
oracle@mqm-testdb2:~$ echo $ORACLE_SID
QPROD2

oracle@mqm-testdb1:/u02/rmanbkp$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 22:45:00 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7257
Next log sequence to archive   7265
Current log sequence           7265
SQL> alter system switch logfile;
System altered.
oracle@mqm-testdb2:~$ echo $ORACLE_SID
QPROD1

Note down the dbid from the primary:

SQL> select dbid from v$database;

DBID
----------
3195744369


Prepare the pfile and nomount the standby database:

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ vi initQPRODN1.ora
QPRODN1.__data_transfer_cache_size=0
QPRODN1.__db_cache_size=11072962560
QPRODN1.__java_pool_size=234881024
QPRODN1.__large_pool_size=2785017856
QPRODN1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
QPRODN1.__pga_aggregate_target=3221225472
QPRODN1.__sga_target=16106127360
QPRODN1.__shared_io_pool_size=0
QPRODN1.__shared_pool_size=1946157056
QPRODN1.__streams_pool_size=0
#*.cluster_database=TRUE
CONTROL_FILES='+REDO/QPRODN/CONTROLFILE/current.1815.945626485','+DATA/QPRODN/CONTROLFILE/current.284.945626485'
*.compatible='12.1.0.2.0'
*.db_file_name_convert='+DATA/QPROD/DATAFILE/','+DATA/QPRODN/DATAFILE'
*.db_name='QPROD'
*.db_recovery_file_dest_size=42949672960
*.db_recovery_file_dest='+REDO'
*.db_unique_name='QPRODN'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srpstbXDB)'
*.fal_server='QPROD'
QPRODN1.instance_number=1
QPRODN2.instance_number=2
QPRODN1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mqm-testdb5-vip)(PORT=1521))))'
QPRODN2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mqm-testdb6-vip)(PORT=1521))))'
*.log_archive_config='DG_CONFIG=(QPROD,QPRODN)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=QPRODN'
*.log_archive_dest_2='service=QPROD valid_for=(online_logfiles,primary_role) db_unique_name=QPROD'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=5
*.log_file_name_convert='+DATA/QPROD/ONLINELOG/','+REDO/QPROD/ONLINELOG/','+DATA/QPRODN/ONLINELOG','+REDO/QPRODN/ONLINELOG'
*.remote_listener='eftstest-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=15g
*.standby_file_management='AUTO'
QPRODN1.thread=1
QPRODN2.thread=2


Start the standby database in nomount.

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 21:29:22 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initQPRODN1.ora';
ORACLE instance started.
Total System Global Area 1.6106E+10 bytes
Fixed Size                  7651632 bytes
Variable Size            4966063824 bytes
Database Buffers         1.1073E+10 bytes
Redo Buffers               59449344 bytes
SQL>

Restore the controlfile, make sure it will be created in asm diskgroup:

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Mar 8 21:30:18 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: QPROD (not mounted)

RMAN> restore standby controlfile from '/u02/QPRODN_BKP08032020/standby.ctl';

Starting restore at 08-MAR-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1441 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+REDO/QPRODN/CONTROLFILE/current.284.1034544631
output file name=+DATA/QPRODN/CONTROLFILE/current.313.1034544631
Finished restore at 08-MAR-20

RMAN> exit

shutdown the database and update pfile with newly created controlfiles;

ex:
vi initQPRODN1.ora
CONTROL_FILES='+REDO/QPRODN/CONTROLFILE/current.284.1034544631','+DATA/QPRODN/CONTROLFILE/current.313.1034544631'


oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ cat initQPRODN1.ora
QPRODN1.__data_transfer_cache_size=0
QPRODN1.__db_cache_size=11072962560
QPRODN1.__java_pool_size=234881024
QPRODN1.__large_pool_size=2785017856
QPRODN1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
QPRODN1.__pga_aggregate_target=3221225472
QPRODN1.__sga_target=16106127360
QPRODN1.__shared_io_pool_size=0
QPRODN1.__shared_pool_size=1946157056
QPRODN1.__streams_pool_size=0
#*.cluster_database=TRUE
CONTROL_FILES='+REDO/QPRODN/CONTROLFILE/current.284.1034544631','+DATA/QPRODN/CONTROLFILE/current.313.1034544631'
*.compatible='12.1.0.2.0'
*.db_file_name_convert='+DATA/QPROD/DATAFILE/','+DATA/QPRODN/DATAFILE'
*.db_name='QPROD'
*.db_recovery_file_dest_size=42949672960
*.db_recovery_file_dest='+REDO'
*.db_unique_name='QPRODN'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srpstbXDB)'
*.fal_server='QPROD'
QPRODN1.instance_number=1
QPRODN2.instance_number=2
QPRODN1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mqm-testdb5-vip)(PORT=1521))))'
QPRODN2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mqm-testdb6-vip)(PORT=1521))))'
*.log_archive_config='DG_CONFIG=(QPROD,QPRODN)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=QPRODN'
*.log_archive_dest_2='service=QPROD valid_for=(online_logfiles,primary_role) db_unique_name=QPROD'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=5
*.log_file_name_convert='+DATA/QPROD/ONLINELOG/','+REDO/QPROD/ONLINELOG/','+DATA/QPRODN/ONLINELOG','+REDO/QPRODN/ONLINELOG'
*.remote_listener='eftstest-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=15g
*.standby_file_management='AUTO'
QPRODN1.thread=1
QPRODN2.thread=2

Startup the database on nomount and issue the alter databse mount command with newly created controlfile:

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 21:34:05 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initQPRODN1.ora';
ORACLE instance started.

Total System Global Area 1.6106E+10 bytes
Fixed Size                  7651632 bytes
Variable Size            4966063824 bytes
Database Buffers         1.1073E+10 bytes
Redo Buffers               59449344 bytes
SQL> alter database mount;
Database altered.

Goto the backup location and verify the backups once before going to restore:

oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$ ls -ltr
total 80502410
-rw-r-----   1 oracle   oinstall 900897792 Mar  8 20:42 QPROD_20200308_93_1_ARCHIVE
-rw-r-----   1 oracle   oinstall 777929728 Mar  8 20:42 QPROD_20200308_94_1_ARCHIVE
-rw-r-----   1 oracle   oinstall 425146368 Mar  8 20:42 QPROD_20200308_95_1_ARCHIVE
-rw-r-----   1 oracle   oinstall 387843072 Mar  8 20:42 QPROD_20200308_96_1_ARCHIVE
-rw-r-----   1 oracle   oinstall 315320832 Mar  8 20:42 QPROD_20200308_97_1_ARCHIVE
-rw-r-----   1 oracle   oinstall 9584386048 Mar  8 20:43 full2puqjgua
-rw-r-----   1 oracle   oinstall 7514497024 Mar  8 20:45 full2quqjgub
-rw-r-----   1 oracle   oinstall 8994889728 Mar  8 20:46 full2ruqjgub
-rw-r-----   1 oracle   oinstall 12259835904 Mar  8 20:49 full2suqjguc
-rw-r-----   1 oracle   oinstall 25165824 Mar  8 20:49 standby.ctl

oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Mar 8 21:35:56 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: QPROD (DBID=3195744369, not open)

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       QPROD   3195744369       PARENT  174129451  07-MAR-17
2       2       QPROD   3195744369       CURRENT 232778945  02-JUN-17


Catalog the rman backup pieces and verify one more time:

RMAN> catalog start with '/u02/QPRODN_BKP08032020';

Starting implicit crosscheck backup at 08-MAR-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1729 device type=DISK
Crosschecked 20 objects
Finished implicit crosscheck backup at 08-MAR-20

Starting implicit crosscheck copy at 08-MAR-20
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 08-MAR-20

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7528.285.1034534711

searching for all files that match the pattern /u02/QPRODN_BKP08032020

List of Files Unknown to the Database
=====================================
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_93_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/full2puqjgua
File Name: /u02/QPRODN_BKP08032020/full2ruqjgub
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_96_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_97_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_94_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_95_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/standby.ctl
File Name: /u02/QPRODN_BKP08032020/full2quqjgub
File Name: /u02/QPRODN_BKP08032020/full2suqjguc

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_93_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/full2puqjgua
File Name: /u02/QPRODN_BKP08032020/full2ruqjgub
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_96_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_97_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_94_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_95_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/standby.ctl
File Name: /u02/QPRODN_BKP08032020/full2quqjgub
File Name: /u02/QPRODN_BKP08032020/full2suqjguc

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       QPROD   3195744369       PARENT  174129451  07-MAR-17
2       2       QPROD   3195744369       CURRENT 232778945  02-JUN-17

RMAN>

Restore the database with rman:

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       QPROD   3195744369       PARENT  174129451  07-MAR-17
2       2       QPROD   3195744369       CURRENT 232778945  02-JUN-17


RMAN> RUN
{
ALLOCATE CHANNEL c6 DEVICE TYPE disk;
ALLOCATE CHANNEL c7 DEVICE TYPE disk;
ALLOCATE CHANNEL c8 DEVICE TYPE disk;
ALLOCATE CHANNEL c9 DEVICE TYPE disk;
restore database;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
}

allocated channel: c6
channel c6: SID=1729 device type=DISK

allocated channel: c7
channel c7: SID=1761 device type=DISK

allocated channel: c8
channel c8: SID=1793 device type=DISK

allocated channel: c9
channel c9: SID=1825 device type=DISK

Starting restore at 08-MAR-20

channel c6: starting datafile backup set restore
channel c6: specifying datafile(s) to restore from backup set
channel c6: restoring datafile 00005 to +DATA/QPRODN/datafileundotbs2.328.945626765
channel c6: restoring datafile 00014 to +DATA/QPRODN/datafilesysaux.357.949233333
channel c6: restoring datafile 00018 to +DATA/QPRODN/datafilesysaux.289.949667263
channel c6: restoring datafile 00022 to +DATA/QPRODN/datafileach_arch_sec.364.955635241
channel c6: restoring datafile 00028 to +DATA/QPRODN/datafileach_arch_sec.410.955635337
channel c6: reading from backup piece /u02/QPRODN_BKP08032020/full2quqjgub
channel c7: starting datafile backup set restore
channel c7: specifying datafile(s) to restore from backup set
channel c7: restoring datafile 00002 to +DATA/QPRODN/datafileach.465.945626749
channel c7: restoring datafile 00003 to +DATA/QPRODN/datafilesysaux.290.945626751
channel c7: restoring datafile 00004 to +DATA/QPRODN/datafileundotbs1.298.945626761
channel c7: restoring datafile 00009 to +DATA/QPRODN/datafileindx.296.945626753
channel c7: restoring datafile 00010 to +DATA/QPRODN/datafilelobspace.297.945626761
channel c7: restoring datafile 00013 to +DATA/QPRODN/datafileach.477.947075519
channel c7: restoring datafile 00015 to +DATA/QPRODN/datafilesysaux.462.949233349
channel c7: restoring datafile 00019 to +DATA/QPRODN/datafilesysaux.306.950083981
channel c7: reading from backup piece /u02/QPRODN_BKP08032020/full2puqjgua
channel c8: starting datafile backup set restore
channel c8: specifying datafile(s) to restore from backup set
channel c8: restoring datafile 00001 to +DATA/QPRODN/datafilesystem.390.945626751
channel c8: restoring datafile 00007 to +DATA/QPRODN/datafileach_arch.334.945626751
channel c8: restoring datafile 00012 to +DATA/QPRODN/datafileach.286.945626751
channel c8: restoring datafile 00017 to +DATA/QPRODN/datafilesysaux.285.949538521
channel c8: restoring datafile 00023 to +DATA/QPRODN/datafileach_arch_sec.375.955635253
channel c8: restoring datafile 00026 to +DATA/QPRODN/datafileach_arch.470.955635313
channel c8: restoring datafile 00027 to +DATA/QPRODN/datafileach_arch.312.955635321
channel c8: restoring datafile 00029 to +DATA/QPRODN/datafilepatrol.389.990708027
channel c8: reading from backup piece /u02/QPRODN_BKP08032020/full2ruqjgub
channel c9: starting datafile backup set restore
channel c9: specifying datafile(s) to restore from backup set
channel c9: restoring datafile 00006 to +DATA/QPRODN/datafileusers.436.945626769
channel c9: restoring datafile 00008 to +DATA/QPRODN/datafileach_rpt.295.945626753
channel c9: restoring datafile 00011 to +DATA/QPRODN/datafileach.408.945626751
channel c9: restoring datafile 00016 to +DATA/QPRODN/datafilesysaux.409.949238837
channel c9: restoring datafile 00020 to +DATA/QPRODN/datafileach_arch_sec.468.950434697
channel c9: restoring datafile 00021 to +DATA/QPRODN/datafileach_rpt_sec.427.950438609
channel c9: restoring datafile 00024 to +DATA/QPRODN/datafileach_arch_sec.388.955635263
channel c9: restoring datafile 00025 to +DATA/QPRODN/datafileach_arch.386.955635301
channel c9: reading from backup piece /u02/QPRODN_BKP08032020/full2suqjguc
channel c6: piece handle=/u02/QPRODN_BKP08032020/full2quqjgub tag=ORCL_FULL
channel c6: restored backup piece 1
channel c6: restore complete, elapsed time: 00:41:36
channel c7: piece handle=/u02/QPRODN_BKP08032020/full2puqjgua tag=ORCL_FULL
channel c7: restored backup piece 1
channel c7: restore complete, elapsed time: 00:42:36
channel c8: piece handle=/u02/QPRODN_BKP08032020/full2ruqjgub tag=ORCL_FULL
channel c8: restored backup piece 1
channel c8: restore complete, elapsed time: 00:44:46
channel c9: piece handle=/u02/QPRODN_BKP08032020/full2suqjguc tag=ORCL_FULL
channel c9: restored backup piece 1
channel c9: restore complete, elapsed time: 00:58:26
Finished restore at 08-MAR-20

released channel: c6
released channel: c7
released channel: c8
released channel: c9


Recover the database with rman:


RMAN> RUN
{
ALLOCATE CHANNEL c6 DEVICE TYPE disk;
ALLOCATE CHANNEL c7 DEVICE TYPE disk;
ALLOCATE CHANNEL c8 DEVICE TYPE disk;
ALLOCATE CHANNEL c9 DEVICE TYPE disk;
recover database;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
}

allocated channel: c6
channel c6: SID=1729 device type=DISK

allocated channel: c7
channel c7: SID=1761 device type=DISK

allocated channel: c8
channel c8: SID=1793 device type=DISK

allocated channel: c9
channel c9: SID=1825 device type=DISK

Starting recover at 08-MAR-20

starting media recovery

channel c6: starting archived log restore to default destination
channel c6: restoring archived log
archived log thread=1 sequence=7625
channel c6: restoring archived log
archived log thread=2 sequence=7264
channel c6: reading from backup piece /u02/QPRODN_BKP08032020/QPROD_20200308_96_1_ARCHIVE
channel c6: piece handle=/u02/QPRODN_BKP08032020/QPROD_20200308_96_1_ARCHIVE tag=ORCL_ARCHIVE
channel c6: restored backup piece 1
channel c6: restore complete, elapsed time: 00:00:03
archived log file name=+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_2_seq_7264.286.1034548749 thread=2 sequence=7264
archived log file name=+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7625.287.1034548749 thread=1 sequence=7625
channel default: deleting archived log(s)
archived log file name=+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_2_seq_7264.286.1034548749 RECID=3 STAMP=1034548749
archived log thread=1 sequence=7625
released channel: c6
released channel: c7
released channel: c8
released channel: c9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/08/2020 22:39:12
ORA-15028: ASM file '+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7625.287.1034548749' not dropped; currently being accessed

Now note down the sequence and perform the recovery:

Recovery Manager complete.
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 22:39:32 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.


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

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/QPRODN/DATAFILE/system.269.1034545139'


Primary side:

Note down the last sequence number of thread 1 and thread 2

ASMCMD> cd 2020_03_08/
ASMCMD> ls
thread_1_seq_7619.771.1034478411
thread_1_seq_7620.2679.1034503371
thread_1_seq_7621.943.1034515519
thread_1_seq_7622.801.1034515531
thread_1_seq_7623.2705.1034518935
thread_1_seq_7624.806.1034528083
thread_2_seq_7258.1057.1034497099
thread_2_seq_7259.778.1034515523
thread_2_seq_7260.1050.1034515531
thread_2_seq_7261.889.1034518937
thread_2_seq_7262.2211.1034520955
thread_2_seq_7263.971.1034528083
ASMCMD> cd ARCHIVELOG/
ASMCMD>
ASMCMD> ls
2020_03_08/
ASMCMD> cd 2020_03_08/
ASMCMD> ls
thread_1_seq_7619.771.1034478411
thread_1_seq_7620.2679.1034503371
thread_1_seq_7621.943.1034515519
thread_1_seq_7622.801.1034515531
thread_1_seq_7623.2705.1034518935
thread_1_seq_7624.806.1034528083
thread_1_seq_7625.2889.1034541371
thread_2_seq_7258.1057.1034497099
thread_2_seq_7259.778.1034515523
thread_2_seq_7260.1050.1034515531
thread_2_seq_7261.889.1034518937
thread_2_seq_7262.2211.1034520955
thread_2_seq_7263.971.1034528083
thread_2_seq_7264.1028.1034541369
ASMCMD> ls
thread_1_seq_7619.771.1034478411
thread_1_seq_7620.2679.1034503371
thread_1_seq_7621.943.1034515519
thread_1_seq_7622.801.1034515531
thread_1_seq_7623.2705.1034518935
thread_1_seq_7624.806.1034528083
thread_1_seq_7625.2889.1034541371
thread_1_seq_7626.797.1034548997
thread_2_seq_7258.1057.1034497099
thread_2_seq_7259.778.1034515523
thread_2_seq_7260.1050.1034515531
thread_2_seq_7261.889.1034518937
thread_2_seq_7262.2211.1034520955
thread_2_seq_7263.971.1034528083
thread_2_seq_7264.1028.1034541369
ASMCMD> ls
thread_1_seq_7619.771.1034478411
thread_1_seq_7620.2679.1034503371
thread_1_seq_7621.943.1034515519
thread_1_seq_7622.801.1034515531
thread_1_seq_7623.2705.1034518935
thread_1_seq_7624.806.1034528083
thread_1_seq_7625.2889.1034541371
thread_1_seq_7626.797.1034548997
thread_2_seq_7258.1057.1034497099
thread_2_seq_7259.778.1034515523
thread_2_seq_7260.1050.1034515531
thread_2_seq_7261.889.1034518937
thread_2_seq_7262.2211.1034520955
thread_2_seq_7263.971.1034528083
thread_2_seq_7264.1028.1034541369
thread_2_seq_7265.2785.1034549121
ASMCMD> exit

In my case log sequence numbers are:

oracle@mqm-testdb1:/u02/rmanbkp$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Mar 8 22:57:05 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: QPROD (DBID=3195744369)

RMAN> backup format '/u02/rmanbkp/%d_%s_%p_%c_%t.arc.bkp' archivelog from sequence 7264 until sequence 7265 thread 1;

Starting backup at 08-MAR-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1064 instance=QPROD1 device type=DISK
specification does not match any archived log in the repository
backup cancelled because there are no files to backup
Finished backup at 08-MAR-20

RMAN> backup format '/u02/rmanbkp/%d_%s_%p_%c_%t.arc.bkp' archivelog from sequence 7264 until sequence 7265 thread 2;

Starting backup at 08-MAR-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=7264 RECID=14888 STAMP=1034541371
input archived log thread=2 sequence=7265 RECID=14891 STAMP=1034549122
channel ORA_DISK_1: starting piece 1 at 08-MAR-20
channel ORA_DISK_1: finished piece 1 at 08-MAR-20
piece handle=/u02/rmanbkp/QPROD_101_1_1_1034549851.arc.bkp tag=TAG20200308T225730 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 08-MAR-20

Starting Control File and SPFILE Autobackup at 08-MAR-20
piece handle=+REDO/QPROD/AUTOBACKUP/2020_03_08/s_1034549877.1014.1034549879 comment=NONE
Finished Control File and SPFILE Autobackup at 08-MAR-20

RMAN> backup format '/u02/rmanbkp/%d_%s_%p_%c_%t.arc.bkp' archivelog from sequence 7625 until sequence 7626 thread 1;

Starting backup at 08-MAR-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7625 RECID=14889 STAMP=1034541372
input archived log thread=1 sequence=7626 RECID=14890 STAMP=1034548997
channel ORA_DISK_1: starting piece 1 at 08-MAR-20
channel ORA_DISK_1: finished piece 1 at 08-MAR-20
piece handle=/u02/rmanbkp/QPROD_103_1_1_1034549887.arc.bkp tag=TAG20200308T225807 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 08-MAR-20

Starting Control File and SPFILE Autobackup at 08-MAR-20
piece handle=+REDO/QPROD/AUTOBACKUP/2020_03_08/s_1034549904.992.1034549909 comment=NONE
Finished Control File and SPFILE Autobackup at 08-MAR-20

RMAN> exit
Recovery Manager complete.

Transfer the older archives from primary to standby:

oracle@mqm-testdb1:/u02/rmanbkp$ pwd
/u02/rmanbkp
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$ ls -ltr
total 81706022
-rw-r-----   1 oracle   asmadmin 7514497024 Mar  8 20:06 full2quqjgub
-rw-r-----   1 oracle   asmadmin 9584386048 Mar  8 20:11 full2puqjgua
-rw-r-----   1 oracle   asmadmin 8994889728 Mar  8 20:12 full2ruqjgub
-rw-r-----   1 oracle   asmadmin 12259835904 Mar  8 20:36 full2suqjguc
-rw-r-----   1 oracle   asmadmin 387843072 Mar  8 20:36 QPROD_20200308_96_1_ARCHIVE
-rw-r-----   1 oracle   asmadmin 425146368 Mar  8 20:36 QPROD_20200308_95_1_ARCHIVE
-rw-r-----   1 oracle   asmadmin 777929728 Mar  8 20:36 QPROD_20200308_94_1_ARCHIVE
-rw-r-----   1 oracle   asmadmin 900897792 Mar  8 20:36 QPROD_20200308_93_1_ARCHIVE
-rw-r-----   1 oracle   asmadmin 315320832 Mar  8 20:36 QPROD_20200308_97_1_ARCHIVE
-rw-r-----   1 oracle   asmadmin 25165824 Mar  8 20:37 standby.ctl
-rw-r-----   1 oracle   asmadmin 396113408 Mar  8 22:57 QPROD_101_1_1_1034549851.arc.bkp
-rw-r-----   1 oracle   asmadmin 219527168 Mar  8 22:58 QPROD_103_1_1_1034549887.arc.bkp
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$ scp QPROD_101_1_1_1034549851.arc.bkp oracle@10.40.135.23:/u02/QPRODN_BKP08032020/
Password:
QPROD_101_1_1_10345 100% |********************************************
oracle@mqm-testdb1:/u02/rmanbkp$ scp QPROD_103_1_1_1034549887.arc.bkp oracle@10.40.135.23:/u02/QPRODN_BKP08032020/
Password:
Password:
QPROD_103_1_1_10345 100% |********************************************

Apply the missing archives in the standby side:


oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 23:01:07 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Mar 8 23:01:16 2020

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

connected to target database: QPROD (DBID=3195744369, not open)

RMAN> catalog start with '/u02/QPRODN_BKP08032020';

using target database control file instead of recovery catalog
searching for all files that match the pattern /u02/QPRODN_BKP08032020

List of Files Unknown to the Database
=====================================
File Name: /u02/QPRODN_BKP08032020/QPROD_101_1_1_1034549851.arc.bkp
File Name: /u02/QPRODN_BKP08032020/QPROD_103_1_1_1034549887.arc.bkp

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u02/QPRODN_BKP08032020/QPROD_101_1_1_1034549851.arc.bkp
File Name: /u02/QPRODN_BKP08032020/QPROD_103_1_1_1034549887.arc.bkp

RMAN> run
{
restore archivelog from logseq=7264 until logseq=7265 thread=2;
restore archivelog from logseq=7625 until logseq=7626 thread=1;
}2> 3> 4> 5>

Starting restore at 08-MAR-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1441 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7264
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7265
channel ORA_DISK_1: reading from backup piece /u02/QPRODN_BKP08032020/QPROD_101_1_1_1034549851.arc.bkp
channel ORA_DISK_1: piece handle=/u02/QPRODN_BKP08032020/QPROD_101_1_1_1034549851.arc.bkp tag=TAG20200308T225730
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-MAR-20

Starting restore at 08-MAR-20
using channel ORA_DISK_1

archived log for thread 1 with sequence 7625 is already on disk as file +REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7625.287.1034548749
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7626
channel ORA_DISK_1: reading from backup piece /u02/QPRODN_BKP08032020/QPROD_103_1_1_1034549887.arc.bkp
channel ORA_DISK_1: piece handle=/u02/QPRODN_BKP08032020/QPROD_103_1_1_1034549887.arc.bkp tag=TAG20200308T225807
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-MAR-20

RMAN> recover database;

Starting recover at 08-MAR-20
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 7625 is already on disk as file +REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7625.287.1034548749
archived log for thread 1 with sequence 7626 is already on disk as file +REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7626.289.1034550181
archived log for thread 2 with sequence 7265 is already on disk as file +REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_2_seq_7265.288.1034550179
archived log file name=+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_2_seq_7265.288.1034550179 thread=2 sequence=7265
archived log file name=+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7625.287.1034548749 thread=1 sequence=7625
archived log file name=+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7626.289.1034550181 thread=1 sequence=7626
media recovery complete, elapsed time: 00:00:03
Finished recover at 08-MAR-20

RMAN> exit

Open the database in read only mode:

Recovery Manager complete.
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 23:03:24 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> alter database open read only;
Database altered.

Modify the standby parameters and mount the database:

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ cat initQPRODN1.ora
QPRODN1.__data_transfer_cache_size=0
QPRODN1.__db_cache_size=11072962560
QPRODN1.__java_pool_size=234881024
QPRODN1.__large_pool_size=2785017856
QPRODN1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
QPRODN1.__pga_aggregate_target=3221225472
QPRODN1.__sga_target=16106127360
QPRODN1.__shared_io_pool_size=0
QPRODN1.__shared_pool_size=1946157056
QPRODN1.__streams_pool_size=0
#*.cluster_database=TRUE
CONTROL_FILES='+REDO/QPRODN/CONTROLFILE/current.284.1034544631','+DATA/QPRODN/CONTROLFILE/current.313.1034544631'
*.compatible='12.1.0.2.0'
*.db_file_name_convert='+DATA/QPROD/DATAFILE/','+DATA/QPRODN/DATAFILE'
*.db_name='QPROD'
*.DB_CREATE_FILE_DEST='+DATA'
*.db_recovery_file_dest_size=42949672960
*.db_recovery_file_dest='+REDO'
*.db_unique_name='QPRODN'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srpstbXDB)'
*.fal_server='QPROD'
QPRODN1.instance_number=1
QPRODN2.instance_number=2
QPRODN1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mqm-testdb5-vip)(PORT=1521))))'
QPRODN2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mqm-testdb6-vip)(PORT=1521))))'
*.log_archive_config='DG_CONFIG=(QPROD,QPRODN)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=QPRODN'
*.log_archive_dest_2='service=QPROD valid_for=(online_logfiles,primary_role) db_unique_name=QPROD'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=5
*.log_file_name_convert='+DATA/QPROD/ONLINELOG/','+REDO/QPROD/ONLINELOG/','+DATA/QPRODN/ONLINELOG','+REDO/QPRODN/ONLINELOG'
*.remote_listener='eftstest-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=15g
*.standby_file_management='AUTO'

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 23:43:20 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initQPRODN1.ora';
ORACLE instance started.
Total System Global Area 1.6106E+10 bytes
Fixed Size                  7651632 bytes
Variable Size            4966063824 bytes
Database Buffers         1.1073E+10 bytes
Redo Buffers               59449344 bytes
SQL> alter database mount;
Database altered.

Drop the older (standby) redolog files:

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE DROP LOGFILE GROUP 7;
ALTER DATABASE DROP LOGFILE GROUP 8;
ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 10;
ALTER DATABASE DROP LOGFILE GROUP 11;
ALTER DATABASE DROP LOGFILE GROUP 12;
ALTER DATABASE DROP LOGFILE GROUP 13;
ALTER DATABASE DROP LOGFILE GROUP 14;
ALTER DATABASE DROP LOGFILE GROUP 15;
ALTER DATABASE DROP LOGFILE GROUP 16;
ALTER DATABASE DROP LOGFILE GROUP 17;
ALTER DATABASE DROP LOGFILE GROUP 18;

SQL> ALTER DATABASE DROP LOGFILE GROUP 9
*
ERROR at line 1:
ORA-01623: log 9 is current log for instance QPRODN1 (thread 1) - cannot drop
ORA-00312: online log 9 thread 1: '+REDO/QPROD/ONLINELOG/group_9'
ORA-00312: online log 9 thread 1: '+REDO/QPROD/ONLINELOG/group_9'


SQL> ALTER DATABASE DROP LOGFILE GROUP 12
*
ERROR at line 1:
ORA-01623: log 12 is current log for instance QPROD1 (thread 2) - cannot drop
ORA-00312: online log 12 thread 2: '+REDO/QPROD/ONLINELOG/group_12'
ORA-00312: online log 12 thread 2: '+REDO/QPROD/ONLINELOG/group_12'


Note:
Current online redolog group will be not dropped, leave it this error and proceed further.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

GROUP# ARC STATUS
---------- --- ----------------
9 NO  CURRENT
12 NO  CURRENT


Add the new redolog groups in standby side:

SQL> ALTER DATABASE  ADD LOGFILE THREAD 1
GROUP 1('+DATA', '+REDO') SIZE 500M,
GROUP 3('+DATA', '+REDO') SIZE 500M,
GROUP 5('+DATA', '+REDO') SIZE 500M,
GROUP 7('+DATA', '+REDO') SIZE 500M,
GROUP 11('+DATA', '+REDO') SIZE 500M,
GROUP 13('+DATA', '+REDO') SIZE 500M,
GROUP 15('+DATA', '+REDO') SIZE 500M,
GROUP 17('+DATA', '+REDO') SIZE 500M ;
Database altered.

SQL> ALTER DATABASE  ADD LOGFILE THREAD 2
GROUP 2('+DATA', '+REDO') SIZE 500M,
GROUP 4('+DATA', '+REDO') SIZE 500M,
GROUP 6('+DATA', '+REDO') SIZE 500M,
GROUP 8('+DATA', '+REDO') SIZE 500M,
GROUP 10('+DATA', '+REDO') SIZE 500M,
GROUP 14('+DATA', '+REDO') SIZE 500M,
GROUP 16('+DATA', '+REDO') SIZE 500M,
GROUP 18('+DATA', '+REDO') SIZE 500M; 
Database altered.

Note: we have total 18 groups since group 9 and 12 are in current state we cannot drop it and re-create it now, we will add it later.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES UNUSED
         9 NO  CURRENT
        17 YES UNUSED
        15 YES UNUSED
        13 YES UNUSED
        11 YES UNUSED
         7 YES UNUSED
         5 YES UNUSED
         3 YES UNUSED
        10 YES UNUSED
        18 YES UNUSED

    GROUP# ARC STATUS
---------- --- ----------------
         8 YES UNUSED
        16 YES UNUSED
         2 YES UNUSED
        12 NO  CURRENT
         4 YES UNUSED
        14 YES UNUSED
         6 YES UNUSED

18 rows selected.


SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initQPRODN1.ora';
ORACLE instance started.

Total System Global Area 1.6106E+10 bytes
Fixed Size                  7651632 bytes
Variable Size            4966063824 bytes
Database Buffers         1.1073E+10 bytes
Redo Buffers               59449344 bytes
SQL> alter database mount;

Database altered.

SQL>
SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES UNUSED
         9 NO  CURRENT
        17 YES UNUSED
        15 YES UNUSED
        13 YES UNUSED
        11 YES UNUSED
         7 YES UNUSED
         5 YES UNUSED
         3 YES UNUSED
        10 YES UNUSED
        18 YES UNUSED

    GROUP# ARC STATUS
---------- --- ----------------
         8 YES UNUSED
        16 YES UNUSED
         2 YES UNUSED
        12 NO  CURRENT
         4 YES UNUSED
        14 YES UNUSED
         6 YES UNUSED

18 rows selected.


Example of dropping a redologfiles:

SQL> ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 9
*
ERROR at line 1:
ORA-01623: log 9 is current log for instance QPRODN1 (thread 1) - cannot drop
ORA-00312: online log 9 thread 1: '+REDO/QPROD/ONLINELOG/group_9'
ORA-00312: online log 9 thread 1: '+REDO/QPROD/ONLINELOG/group_9'


SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

SQL> alter database open read only;
Database altered.

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initQPRODN1.ora';
ORACLE instance started.

Total System Global Area 1.6106E+10 bytes
Fixed Size                  7651632 bytes
Variable Size            4966063824 bytes
Database Buffers         1.1073E+10 bytes
Redo Buffers               59449344 bytes
SQL> alter database mount;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 9
*
ERROR at line 1:
ORA-01623: log 9 is current log for instance QPRODN1 (thread 1) - cannot drop
ORA-00312: online log 9 thread 1: '+REDO/QPROD/ONLINELOG/group_9'
ORA-00312: online log 9 thread 1: '+REDO/QPROD/ONLINELOG/group_9'


Now drop the standby redolog files and re-create it in standby side:

SQL> alter database drop standby logfile group 19;
alter database drop standby logfile group 20;
alter database drop standby logfile group 21;
alter database drop standby logfile group 22;
alter database drop standby logfile group 23;
alter database drop standby logfile group 24;
alter database drop standby logfile group 25;
alter database drop standby logfile group 26;
alter database drop standby logfile group 27;
alter database drop standby logfile group 28;
alter database drop standby logfile group 29;
alter database drop standby logfile group 30;
alter database drop standby logfile group 31;
alter database drop standby logfile group 32;
alter database drop standby logfile group 33;
alter database drop standby logfile group 34;
alter database drop standby logfile group 35;
alter database drop standby logfile group 36;
alter database drop standby logfile group 37;
alter database drop standby logfile group 38;
alter database drop standby logfile group 39;
alter database drop standby logfile group 40;
alter database drop standby logfile group 41;
alter database drop standby logfile group 42;
alter database drop standby logfile group 43;
alter database drop standby logfile group 44;
alter database drop standby logfile group 45;
alter database drop standby logfile group 46;
alter database drop standby logfile group 47;
alter database drop standby logfile group 48;
alter database drop standby logfile group 49;
alter database drop standby logfile group 50;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 19 ('+DATA', '+REDO') SIZE 500M,
GROUP 20 ('+DATA', '+REDO') SIZE 500M,
GROUP 21 ('+DATA', '+REDO') SIZE 500M,
GROUP 22 ('+DATA', '+REDO') SIZE 500M,
GROUP 23 ('+DATA', '+REDO') SIZE 500M,
GROUP 24 ('+DATA', '+REDO') SIZE 500M,
GROUP 25 ('+DATA', '+REDO') SIZE 500M,
GROUP 26 ('+DATA', '+REDO') SIZE 500M,
GROUP 27 ('+DATA', '+REDO') SIZE 500M,
GROUP 28 ('+DATA', '+REDO') SIZE 500M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 29 ('+DATA', '+REDO') SIZE 500M,
GROUP 30 ('+DATA', '+REDO') SIZE 500M,
GROUP 31 ('+DATA', '+REDO') SIZE 500M,
GROUP 32 ('+DATA', '+REDO') SIZE 500M,
GROUP 33 ('+DATA', '+REDO') SIZE 500M,
GROUP 34 ('+DATA', '+REDO') SIZE 500M,
GROUP 35 ('+DATA', '+REDO') SIZE 500M,
GROUP 36 ('+DATA', '+REDO') SIZE 500M,
GROUP 37 ('+DATA', '+REDO') SIZE 500M,
GROUP 38 ('+DATA', '+REDO') SIZE 500M;

Take a backup and create spfile in asm diskgroup with a pfile.

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ cp initQPRODN1.ora initQPRODN1.ora_org09032020

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 00:52:46 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initQPRODN1.ora';
ORACLE instance started.

Total System Global Area 1.6106E+10 bytes
Fixed Size                  7651632 bytes
Variable Size            4966063824 bytes
Database Buffers         1.1073E+10 bytes
Redo Buffers               59449344 bytes
SQL> create spfile='+DATA/QPRODN/SPFILE/spfileeftstn.ora' from pfile;
File created.
SQL> exit

Copy the pfile to node2 and provide the spfile location in pfile.

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ scp initQPRODN1.ora oracle@10.40.135.24:/u01/app/oracle/product/12.1.0/db_1/dbs
initQPRODN1.ora     100% |***********************************************************************************************************************************************
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ ssh oracle@NODE2

oracle@mqm-testdb6:/u01/app/oracle/product/12.1.0/db_1/dbs$ mv initQPRODN1.ora initQPRODN2.ora
oracle@mqm-testdb6:/u01/app/oracle/product/12.1.0/db_1/dbs$
oracle@mqm-testdb6:/u01/app/oracle/product/12.1.0/db_1/dbs$
oracle@mqm-testdb6:/u01/app/oracle/product/12.1.0/db_1/dbs$ cat initQPRODN2.ora
spfile='+DATA/QPRODN/SPFILE/spfileeftstn.ora'

Remove and add the $ORACLE_HOME through srvctl:

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$srvctl add database -db QPRODN -o /u01/app/oracle/product/12.1.0/db_1 -startoption mount -role physical_standby -pwfile +DATA/QPRODN/PASSWORD/orrapwQPRODs
PRCD-1000 : Database QPRODn already exists
PRCR-1086 : resource ora.QPRODn.db is already registered
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ srvctl remove database -db QPRODN
Remove the database QPRODN? (y/[n]) yes

rapwQPRODs-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ srvctl add database -db QPRODN -o /u01/app/oracle/product/12.1.0/db_1 -startoption mount -role physical_standby -pwfile +DATA/QPRODN/PASSWORD/or
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ srvctl add instance -instance QPRODN -db QPRODn -node mqm-testdb5

Add instance through srvctl:

oracle@mqm-testdb6:~$ srvctl add instance -instance QPRODN2 -db QPRODn -node mqm-testdb6

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ srvctl modify database -d QPRODN -spfile '+DATA/QPRODN/SPFILE/spfileQPRODN.ora'

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ srvctl config database -d QPRODN
Database unique name: QPRODN
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/QPRODN/SPFILE/spfileQPRODN.ora
Password file: +DATA/QPRODN/PASSWORD/orapwQPROD
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: QPRODN1,QPRODN2
Configured nodes: mqm-testdb5,mqm-testdb6
Database is administrator managed

Startup the database and check the mrp status:

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 01:03:09 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.6106E+10 bytes
Fixed Size                  7651632 bytes
Variable Size            4966063824 bytes
Database Buffers         1.1073E+10 bytes
Redo Buffers               59449344 bytes
SQL> alter database mount standby database;
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
ARCH      CONNECTED             0          0

SQL> /

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0

SQL>

racle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl config scan
SCAN name: mqmtest-scan, Network: 1
Subnet IPv4: 10.50.125.0/255.255.255.0/net0, static
Subnet IPv6:
SCAN 0 IPv4 VIP: 10.50.125.19
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 1 IPv4 VIP: 10.50.125.17
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 2 IPv4 VIP: 10.50.125.18
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:

Add service through srvctl:

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl add service -database QPRODN -service QPRODNSN -preferred QPRODN1,QPRODN2

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl ENABLE SERVICE -database QPRODN -service QPRODNSN
PRCC-1010 : QPRODNSN was already enabled
PRCR-1002 : Resource ora.QPRODn.QPRODnsn.svc is already enabled

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl START SERVICE -database QPRODN -service QPRODNSN
PRCD-1084 : Failed to start service QPRODNSN
PRCR-1079 : Failed to start resource ora.QPRODn.QPRODnsn.svc
CRS-2800: Cannot start resource 'ora.QPRODn.db' as it is already in the INTERMEDIATE state on server 'mqm-testdb5'
CRS-2632: There are no more servers to try to place resource 'ora.QPRODn.QPRODnsn.svc' on that would satisfy its placement policy
CRS-2674: Start of 'ora.QPRODn.db' on 'mqm-testdb6' failed
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 01:43:31 2020

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


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

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 01:45:40 2020

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


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

SQL> show parameter dispatcher;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (PROTOCOL=TCP) (SERVICE=srpstb
                                                 XDB)
max_dispatchers                      integer
SQL> alter system set dispatchers='(PROTOCOL=TCP) (SERVICE=QPRODNXDB)' scope=both sid='*';

System altered.

SQL> show parameter dispatcher;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (PROTOCOL=TCP) (SERVICE=QPROD
                                                 NXDB)
max_dispatchers                      integer
SQL> exit

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): mqm-testdb6,mqm-testdb5
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl stop listener
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl start listener
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): mqm-testdb6,mqm-testdb5
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 02:04:14 2020

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


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

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
ARCH      CONNECTED             0          0

SQL> exit

oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl stop instance -d QPRODN -i QPRODN2

oracle@mqm-testdb5:/tmp$ ls -ltrh pwdQPROD
-rw-r-----   1 oracle   oinstall    7.5K Mar  9 02:17 pwdQPROD
oracle@mqm-testdb5:/tmp$ pwd
/tmp
oracle@mqm-testdb5:/tmp$ srvctl config database
QPRODN
oracle@mqm-testdb5:/tmp$ srvctl add database -db QPRODN -o /u01/app/oracle/product/12.1.0/db_1 -startoption mount -role physical_standby -pwfile +DATA/QPRODN/PASSWORD/pwdQPROD
PRCS-1007 : Server pool QPRODN already exists
PRCR-1086 : server pool ora.QPRODN is already registered

oracle@mqm-testdb5:/tmp$ srvctl status database -db QPRODN
Instance QPRODN1 is running on node mqm-testdb5
Instance QPRODN2 is not running on node mqm-testdb6

oracle@mqm-testdb5:/tmp$ srvctl config database -db QPRODN
Database unique name: QPRODN
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/QPRODN/SPFILE/spfileQPRODN.ora
Password file: +DATA/QPRODN/PASSWORD/orapwQPROD
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: REDO,DATA
Mount point paths:
Services: QPRODNSN
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: QPRODN1,QPRODN2
Configured nodes: mqm-testdb5,mqm-testdb6
Database is administrator managed

oracle@mqm-testdb5:/tmp$ srvctl modify database -db QPRODN -o /u01/app/oracle/product/12.1.0/db_1 -startoption mount -role physical_standby -pwfile +DATA/QPRODN/PASSWORD/pwdQPROD

oracle@mqm-testdb5:/tmp$ srvctl config database -db QPRODN
Database unique name: QPRODN
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/QPRODN/SPFILE/spfileQPRODN.ora
Password file: +DATA/QPRODN/PASSWORD/pwdQPROD
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: REDO,DATA
Mount point paths:
Services: QPRODNSN
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: QPRODN1,QPRODN2
Configured nodes: mqm-testdb5,mqm-testdb6
Database is administrator managed

oracle@mqm-testdb5:/tmp$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 02:30:18 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

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      CLOSING            7631          1
ARCH      CLOSING            7273          2
RFS       IDLE                  0          0
RFS       IDLE               7632          1
RFS       IDLE                  0          0
RFS       IDLE               7274          2
RFS       IDLE                  0          0
RFS       IDLE                  0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0

15 rows selected.

SQL> alter database recover managed standby database disconnect from session;

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      CLOSING            7631          1
ARCH      CLOSING            7273          2
RFS       IDLE                  0          0
RFS       IDLE               7632          1
RFS       IDLE                  0          0
RFS       IDLE               7274          2
RFS       IDLE                  0          0
RFS       IDLE                  0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
MRP0      APPLYING_LOG       7632          1

16 rows selected.

SQL> select name,open_mode,database_role,cdb from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    CDB
--------- -------------------- ---------------- ---
QPROD    MOUNTED              PHYSICAL STANDBY NO



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

SQL> @sync.sql;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                   7631                  7631          0
         2                   7273                  7273          0



oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:37:27 2020

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


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

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

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CLOSING            7632          1
ARCH      CLOSING            7631          1
ARCH      CLOSING            7274          2
RFS       IDLE                  0          0
RFS       IDLE               7633          1
RFS       IDLE                  0          0
RFS       IDLE               7275          2
RFS       IDLE                  0          0
RFS       IDLE                  0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
MRP0      APPLYING_LOG       7275          2

16 rows selected.

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

   THREAD#     GROUP# STATUS           BYTES/1024/1024
---------- ---------- ---------------- ---------------
         1          1 UNUSED                       500
         1          9 CLEARING                     500
         1         17 UNUSED                       500
         1         15 UNUSED                       500
         1         13 UNUSED                       500
         1         11 UNUSED                       500
         1          7 UNUSED                       500
         1          5 UNUSED                       500
         1          3 UNUSED                       500
         2         10 UNUSED                       500
         2         18 UNUSED                       500

   THREAD#     GROUP# STATUS           BYTES/1024/1024
---------- ---------- ---------------- ---------------
         2          8 UNUSED                       500
         2         16 UNUSED                       500
         2          2 UNUSED                       500
         2         12 CLEARING                     500
         2          4 UNUSED                       500
         2         14 UNUSED                       500
         2          6 UNUSED                       500

18 rows selected.

SQL> ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 9
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL> alter system set standby_file_management=’MANUAL;^C

SQL>
SQL>
SQL> alter system set standby_file_management='MANUAL';

System altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 9;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 12;

Database altered.

SQL> ALTER DATABASE  ADD LOGFILE THREAD 1 GROUP 9('+DATA', '+REDO') SIZE 500M;

Database altered.

SQL> ALTER DATABASE  ADD LOGFILE THREAD 2 GROUP 12('+DATA', '+REDO') SIZE 500M;

Database altered.

SQL> alter system set standby_file_management='AUTO';

System altered.

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

   THREAD#     GROUP# STATUS           BYTES/1024/1024
---------- ---------- ---------------- ---------------
         1          1 UNUSED                       500
         1          9 UNUSED                       500
         1         17 UNUSED                       500
         1         15 UNUSED                       500
         1         13 UNUSED                       500
         1         11 UNUSED                       500
         1          7 UNUSED                       500
         1          5 UNUSED                       500
         1          3 UNUSED                       500
         2         10 UNUSED                       500
         2         18 UNUSED                       500

   THREAD#     GROUP# STATUS           BYTES/1024/1024
---------- ---------- ---------------- ---------------
         2          8 UNUSED                       500
         2         16 UNUSED                       500
         2          2 UNUSED                       500
         2         12 UNUSED                       500
         2          4 UNUSED                       500
         2         14 UNUSED                       500
         2          6 UNUSED                       500

18 rows selected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.


oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:49:14 2020

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


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


SQL> @syn.sql;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                   7632                  7632          0
         2                   7274                  7274          0

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:49:46 2020

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


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

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

   THREAD#     GROUP# STATUS           BYTES/1024/1024
---------- ---------- ---------------- ---------------
         1          1 UNUSED                       500
         1          9 UNUSED                       500
         1         17 UNUSED                       500
         1         15 UNUSED                       500
         1         13 UNUSED                       500
         1         11 UNUSED                       500
         1          7 UNUSED                       500
         1          5 UNUSED                       500
         1          3 UNUSED                       500
         2         10 UNUSED                       500
         2         18 UNUSED                       500

   THREAD#     GROUP# STATUS           BYTES/1024/1024
---------- ---------- ---------------- ---------------
         2          8 UNUSED                       500
         2         16 UNUSED                       500
         2          2 UNUSED                       500
         2         12 UNUSED                       500
         2          4 UNUSED                       500
         2         14 UNUSED                       500
         2          6 UNUSED                       500

18 rows selected.

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

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CLOSING            7632          1
ARCH      CLOSING            7631          1
ARCH      CLOSING            7274          2
RFS       IDLE                  0          0
RFS       IDLE               7633          1
RFS       IDLE                  0          0
RFS       IDLE               7275          2
RFS       IDLE                  0          0
RFS       IDLE                  0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
MRP0      APPLYING_LOG       7633          1

16 rows selected.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:50:27 2020

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


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

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

oracle@mqm-testdb5:~$ srvctl start instance -d QPRODN -i QPRODN2

oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:51:56 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> alter database open read only;

Database altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options


oracle@mqm-testdb6:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:52:42 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.


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

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open read only;

Database altered.


oracle@mqm-testdb6:~$ srvctl status database -d QPRODN
Instance QPRODN1 is running on node mqm-testdb5
Instance QPRODN2 is running on node mqm-testdb6
oracle@mqm-testdb6:~$


oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:54:00 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select name,open_mode,database_role,cdb from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    CDB
--------- -------------------- ---------------- ---
QPROD    READ ONLY            PHYSICAL STANDBY NO

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

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CLOSING            7632          1
ARCH      CLOSING            7631          1
ARCH      CLOSING            7274          2
RFS       IDLE                  0          0
RFS       IDLE               7633          1
RFS       IDLE                  0          0
RFS       IDLE               7275          2
RFS       IDLE                  0          0
RFS       IDLE                  0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0

15 rows selected.

SQL> alter database recover managed standby database disconnect from session;

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      CLOSING            7632          1
ARCH      CLOSING            7631          1
ARCH      CLOSING            7274          2
RFS       IDLE                  0          0
RFS       IDLE               7633          1
RFS       IDLE                  0          0
RFS       IDLE               7275          2
RFS       IDLE                  0          0
RFS       IDLE                  0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
MRP0      APPLYING_LOG       7633          1

16 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> select name,open_mode,database_role,cdb from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    CDB
--------- -------------------- ---------------- ---
QPROD    READ ONLY WITH APPLY PHYSICAL STANDBY NO

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 12:00:17 2020

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


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

SQL> show parameter dg_broker_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE
SQL>
SQL>
SQL>
SQL> exit

oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:11:26 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> alter database recover managed standby database cancel;

Database altered.


oracle@mqm-testdb5:~$ srvctl config database -d QPRODN
Database unique name: QPRODN
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/QPRODN/SPFILE/spfileQPRODN.ora
Password file: +DATA/QPRODN/PASSWORD/pwdQPROD
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: REDO,DATA
Mount point paths:
Services: QPRODNSN
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: QPRODN1,QPRODN2
Configured nodes: mqm-testdb5,mqm-testdb6
Database is administrator managed
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$ srvctl config database -d QPRODN
Database unique name: QPRODN
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/QPRODN/SPFILE/spfileQPRODN.ora
Password file: +DATA/QPRODN/PASSWORD/pwdQPROD
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: REDO,DATA
Mount point paths:
Services: QPRODNSN
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: QPRODN1,QPRODN2
Configured nodes: mqm-testdb5,mqm-testdb6
Database is administrator managed

oracle@mqm-testdb5:~$ srvctl modify database -d QPRODN -pwfile '+DATA/QPRODN/PASSWORD/pwdQPROD'


oracle@mqm-testdb5:~$ srvctl config database -d QPRODN
Database unique name: QPRODN
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/QPRODN/SPFILE/spfileQPRODN.ora
Password file: +DATA/QPRODN/PASSWORD/pwdQPROD
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: REDO,DATA
Mount point paths:
Services: QPRODNSN
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: QPRODN1,QPRODN2
Configured nodes: mqm-testdb5,mqm-testdb6
Database is administrator managed


oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:40:33 2020

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


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

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

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CLOSING            7275          2
ARCH      CONNECTED             0          0
ARCH      CLOSING            7633          1
ARCH      CLOSING            7631          1
ARCH      CLOSING            7274          2
RFS       IDLE                  0          0
RFS       IDLE               7634          1
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0

14 rows selected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.


oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:56:42 2020

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


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

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/12.1.0
                                                 /db_1/dbs/dr1QPRODN.dat
dg_broker_config_file2               string      /u01/app/oracle/product/12.1.0
                                                 /db_1/dbs/dr2QPRODN.dat
SQL>
SQL>
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
QPRODN1

SQL>
SQL>
SQL>
SQL> alter system set dg_broker_start=FALSE scope=both sid='*';

System altered.

SQL> alter system set dg_broker_config_file1='+DATA/QPRODN/DGBROKERCONFIGFILE/dr1QPRODN.dat' scope=both sid='*';

System altered.

SQL> alter system set dg_broker_config_file2='+DATA/QPRODN/DGBROKERCONFIGFILE/dr2QPRODN.dat' scope=both sid='*';

System altered.

SQL> alter system set dg_broker_start=TRUE scope=both sid='*';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='' scope=both;

System altered.

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

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CLOSING            7275          2
ARCH      CONNECTED             0          0
ARCH      CLOSING            7634          1
ARCH      CLOSING            7631          1
ARCH      CLOSING            7274          2
RFS       IDLE               7277          2
MRP0      APPLYING_LOG       7277          2
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
RFS       IDLE                  0          0
RFS       IDLE                  0          0

13 rows selected.

SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$ dgmgrl
DGMGRL for Solaris: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys_1234@QPRODN
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - testrac_dg

  Protection Mode: MaxPerformance
  Members:
  QPROD  - Primary database
    QPRODn - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 53 seconds ago)


oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 14:47:28 2020

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


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

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

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CLOSING            7275          2
ARCH      CONNECTED             0          0
ARCH      CLOSING            7634          1
ARCH      CLOSING            7631          1
ARCH      CLOSING            7274          2
RFS       IDLE               7277          2
MRP0      APPLYING_LOG       7277          2
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
RFS       IDLE                  0          0
RFS       IDLE                  0          0

13 rows selected.

SQL> @syn.sql;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                   7634                  7634          0
         2                   7276                  7276          0

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$ srvctl status database -d QPRODN
Instance QPRODN1 is running on node mqm-testdb5
Instance QPRODN2 is running on node mqm-testdb6