Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified Associate.

Search This Blog

Tuesday, November 28, 2017

Failover to Stanbdy database in Oracle Cloud using dataguard

Failover to Stanbdy database in Oracle Cloud using dataguard



Below I will show you how to
  1. Create two Oracle databases in Oracle Cloud
  2. Duplicate Primary database to Standby in Oracle Cloud
  3. Configure REAL TIME Apply feature
  4. Manual Switchover to Standby Database in the Cloud
  5. Reinstate the Standby database
  6. Configure Dataguard broker
  7. Failover using dataguard
  8. Reinstate the standby database using FLASHBACK


Before we begin create the storage container for our databases.

Create a storage container for our database.


I have created the container called db_failover with no encryption:


Note down the REST End point for your storage:
https://salescloud44.storage.oraclecloud.com/v1/Storage-salescloud44/db_failover


Now go to the database classic and create the Primary database:

Click on create Service:






- For Service Name enter ERPPRIMARY.
- From the Software Release list, select Oracle Database 12c Release 2.
- From the Software Edition list, select Enterprise Edition.
- From the Database Type list, select Single Instance.

Then click Next to continue.




In the Service Details:

- For DB Name (SID), enter ERPPRD.
- Set an administrative password of your choice and confirm the password (this will be your sys password).
- For Usable Database Storage (GB),  Leave it as default.
- From the Compute Shape list, select OC3 -1 OCPU, 7.5GB RAM (this is the bare minimum required).
- For SSH Public Key, upload the .pub key file or paste in the pop-up window
- For port - Choose the appropriate port - I picked 1621 below




Verify the Summary and click on create:





While the primary is being created, let’s go ahead and create another database for Standby:







Create the Standby Database:




In the Service Details:

- For DB Name (SID), enter erpprd.
- Set an administrative password of your choice and confirm the password (this will be your sys password).
- For Usable Database Storage (GB), Leave it as default.
- From the Compute Shape list, select OC3 -1 OCPU, 7.5GB RAM (this is the bare minimum required).
- For SSH Public Key, upload the .pub key file or paste in the pop-up window.
- For port - Choose the appropriate port - I picked 1621 below




Review the summary and click on create:






Now relax and get a cup of coffee.  It took me an hr to create the instance.




Now enable the rules to allow the ssh/em/listener traffic to get in.










Careful: Only enable rules that are appropriate.



Do the same on the Standby Server:








Test the connectivity and URLs:

Note down the public IP and test the ssh port and listener port are reachable:




shaiks@shaikslinux>nc -vz  129.150.122.62 22
Connection to 129.150.122.62 22 port [tcp/ssh] succeeded!

shaiks@shaikslinux>nc -vz  129.150.122.62 1621
Connection to 129.150.122.62 1621 port [tcp/*] succeeded!


shaiks@shaikslinux>ssh  oracle@129.150.122.62
The authenticity of host '129.150.122.62 (129.150.122.62)' can't be established.
Are you sure you want to continue connecting (yes/no)? yes

[oracle@ERPPrimary ~]$

[oracle@ERPPrimary ~]$ dbaascli
DBAAS CLI version 1.0.0
DBAAS>
DBAAS>quit
Exiting Command Line Interface


[oracle@ERPPrimary ~]$ ps -ef| grep smon;ps -ef| grep tns
oracle    8729     1  0 15:28 ?        00:00:00 ora_smon_ERPPRD
oracle   19821 19499  0 16:48 pts/0    00:00:00 grep smon
root        22     2  0 15:11 ?        00:00:00 [netns]
oracle    7796     1  0 15:27 ?        00:00:00 /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle   19823 19499  0 16:48 pts/0    00:00:00 grep tns


[oracle@ERPPrimary ~]$ tnsping erpprd

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2017 16:48:39

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ERPPrimary.compute-586447955.oraclecloud.internal)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ERPPRD.586447955.oraclecloud.internal)))
OK (10 msec)



Test the connectivity on the Standby:





shaiks@shaikslinux>nc -vz 129.150.123.167 22
Connection to 129.150.123.167 22 port [tcp/ssh] succeeded!

shaiks@shaikslinux>nc -vz 129.150.123.167 1621
Connection to 129.150.123.167 1621 port [tcp/*] succeeded!

shaiks@shaikslinux>ssh opc@129.150.123.167
The authenticity of host '129.150.123.167 (129.150.123.167)' can't be established.

Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '129.150.123.167' (RSA) to the list of known hosts.


[opc@ERPStandby ~]$ sudo su -
[root@ERPStandby ~]# sudo su - oracle
[oracle@ERPStandby ~]$ ps -ef| grep smon;ps -ef| grep tns
oracle    8803     1  0 15:45 ?        00:00:00 ora_smon_ERPPRD
oracle   18614     1  0 16:56 ?        00:00:00 /usr/bin/perl /var/opt/oracle/misc/dbcsmonitor.pl /var/opt/oracle/tfa/erpstandby/tfa_home /var/opt/oracle/tfa/repository/suptools/erpstandby/dbcsmon /var/opt/oracle/tfa/repository/suptools/erpstandby/dbcsmon/oracle
oracle   19351 19273  0 17:01 pts/0    00:00:00 grep smon
root        22     2  0 15:28 ?        00:00:00 [netns]
oracle    7810     1  0 15:44 ?        00:00:00

/u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit



Drop the standby database and we will recreate it from the backup of the primary database:



[oracle@ERPStandby ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 21 17:05:53 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name from v$database;

NAME
---------
ERPPRD


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

SQL> startup mount restrict;
ORACLE instance started.

Total System Global Area 2768240640 bytes
Fixed Size                  8796624 bytes
Variable Size             704644656 bytes
Database Buffers         1979711488 bytes
Redo Buffers               75087872 bytes
Database mounted.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exit





Add the DGMRL static entries in the listener.ora files on both primary and Standby:


On Standby:


[oracle@ERPStandby ~]$ cd $ORACLE_HOME/network/admin
[oracle@ERPStandby admin]$ vi listener.ora


# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/li
stener.ora
# Generated by Oracle configuration tools.


SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = ERPPRDSTBY_DGMGRL)
       (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
       (SID_NAME = ERPPRDSTBY)
    )

      (SID_DESC =
       (GLOBAL_DBNAME = ERPPRDSTBY)
       (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
       (SID_NAME = ERPPRDSTBY)
    )
      (SID_DESC =
       (GLOBAL_DBNAME = ERPPRDSTBY_DGB)
       (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
       (SID_NAME = ERPPRDSTBY)
    )
)


LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = ERPStandby.compute 586447955.oraclecloud.internal)(PORT =
1621))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
   )
 )

VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON
SSL_VERSION = 1.2




On Primary:
[oracle@ERPPrimary ~]$ vi $ORACLE_HOME/network/admin/listener.ora
[oracle@ERPPrimary ~]$


SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = ERPPRD_DGMGRL)
       (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
       (SID_NAME = ERPPRD)
    )
      (SID_DESC =
       (GLOBAL_DBNAME = ERPPRD)
       (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
       (SID_NAME = ERPPRD)
    )
      (SID_DESC =
       (GLOBAL_DBNAME = ERPPRD_DGB)
       (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
       (SID_NAME = ERPPRD)
    )
)

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = ERPPrimary.compute-586447955.oraclecloud.internal)(PORT = 1621))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621))
   )
 )

VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON
SSL_VERSION = 1.2



Restart the listener

[oracle@ERPPrimary ~]$ for i in status stop start status;do lsnrctl $i;done


status listener

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2017 17:37:41

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ERPPrimary.compute-586447955.oraclecloud.internal)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                21-NOV-2017 17:36:25
Uptime                    0 days 0 hr. 1 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ERPPrimary/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ERPPrimary.compute-586447955.oraclecloud.internal)(PORT=1621)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ERPPrimary.compute-586447955.oraclecloud.internal)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ERPPRD/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "4e82b9d2d3a83428e053a243c40a3a74.586447955.oraclecloud.internal" has 1 instance(s).
 Instance "ERPPRD", status READY, has 1 handler(s) for this service...
Service "ERPPRD" has 1 instance(s).
 Instance "ERPPRD", status UNKNOWN, has 1 handler(s) for this service...
Service "ERPPRD.586447955.oraclecloud.internal" has 1 instance(s).
 Instance "ERPPRD", status READY, has 1 handler(s) for this service...
Service "ERPPRD.586447955.oraclecloud.internalXDB" has 1 instance(s).
 Instance "ERPPRD", status READY, has 1 handler(s) for this service...
Service "ERPPRD_DGMGRL" has 1 instance(s).
 Instance "ERPPRD", status UNKNOWN, has 1 handler(s) for this service...
Service "pdb1.586447955.oraclecloud.internal" has 1 instance(s).
 Instance "ERPPRD", status READY, has 1 handler(s) for this service...
The command completed successfully
stop listener

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2017 17:37:41

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ERPPrimary.compute-586447955.oraclecloud.internal)(PORT=1621)))
The command completed successfully
start listener

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2017 17:37:41

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ERPPrimary/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ERPPrimary.compute-586447955.oraclecloud.internal)(PORT=1621)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ERPPrimary.compute-586447955.oraclecloud.internal)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                21-NOV-2017 17:37:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ERPPrimary/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ERPPrimary.compute-586447955.oraclecloud.internal)(PORT=1621)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "ERPPRD" has 1 instance(s).
 Instance "ERPPRD", status UNKNOWN, has 1 handler(s) for this service...
Service "ERPPRD_DGMGRL" has 1 instance(s).
 Instance "ERPPRD", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
status listener

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2017 17:37:41

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ERPPrimary.compute-586447955.oraclecloud.internal)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                21-NOV-2017 17:37:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ERPPrimary/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ERPPrimary.compute-586447955.oraclecloud.internal)(PORT=1621)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "ERPPRD" has 1 instance(s).
 Instance "ERPPRD", status UNKNOWN, has 1 handler(s) for this service...
Service "ERPPRD_DGMGRL" has 1 instance(s).
 Instance "ERPPRD", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully



Do the same on Standby:




[oracle@ERPStandby admin]$ vi listener.ora
[oracle@ERPStandby admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2017 17:39:31

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ERPStandby.compute-586447955.oraclecloud.internal)(PORT=1621)))
The command completed successfully

[oracle@ERPStandby admin]$ lsnrctl stop

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2017 17:39:36

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ERPStandby.compute-586447955.oraclecloud.internal)(PORT=1621)))
The command completed successfully

[oracle@ERPStandby admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2017 17:39:38

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ERPStandby/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ERPStandby.compute-586447955.oraclecloud.internal)(PORT=1621)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ERPStandby.compute-586447955.oraclecloud.internal)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                21-NOV-2017 17:39:38
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ERPStandby/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ERPStandby.compute-586447955.oraclecloud.internal)(PORT=1621)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "ERPPRDSTBY" has 1 instance(s).
 Instance "ERPPRDSTBY", status UNKNOWN, has 1 handler(s) for this service...
Service "ERPPRDSTBY_DGMGRL" has 1 instance(s).
 Instance "ERPPRDSTBY", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


Configure the tnsnames.ora files:


[oracle@ERPStandby admin]$ tnsping ERPPRD

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2017 18:02:20

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 129.150.122.62)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ERPPRD.586447955.oraclecloud.internal)))
OK (0 msec)


[oracle@ERPStandby admin]$ tnsping ERPPRDstby

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2017 18:02:34

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ERPStandby.compute-586447955.oraclecloud.internal)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ERPPRDSTBY.586447955.oraclecloud.internal)))
OK (0 msec)


On Primary:
[oracle@ERPPrimary admin]$ vi tnsnames.ora


[oracle@ERPPrimary admin]$ tnsping erpprd

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2017 18:36:51

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ERPPrimary.compute-586447955.oraclecloud.internal)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ERPPRD.586447955.oraclecloud.internal)))
OK (0 msec)


[oracle@ERPPrimary admin]$ tnsping erpprdstby

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-NOV-2017 18:36:53

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 129.150.123.167)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ERPPRDSTBY.586447955.oraclecloud.internal)))
OK (0 msec)



Create pfile and necessary directories on the Standby:

[oracle@ERPStandby dbs]$ cp  initERPPRD.ora initERPPRDSTBY.ora

[oracle@ERPStandby dbs]$ vi initERPPRDSTBY.ora

Make sure the db_name is change to Primary’s name:

[oracle@ERPStandby dbs]$ grep name initERPPRDSTBY.ora
*.db_name='ERPPRD'
*.db_unique_name='ERPPRDSTBY'
*.instance_name='ERPPRDSTBY'
*.service_names='ERPPRDSTBY.586447955.oraclecloud.internal'




[oracle@ERPStandby dbs]$ mkdir -p /u01/app/oracle/admin/ERPPRDSTBY/adump /u02/app/oracle/oradata/E
RPPRDSTBY/ /u03/app/oracle/fast_recovery_area/ERPPRDSTBY/


Start the standby instance in nomount state:


[oracle@ERPStandby dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 21 18:45:39 2017

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initERPPRDSTBY.ora';
ORACLE instance started.

Total System Global Area 2768240640 bytes
Fixed Size                  8796624 bytes
Variable Size             704644656 bytes
Database Buffers         1979711488 bytes
Redo Buffers               75087872 bytes





Prep the primary -
Set the parameters on Primary:
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(ERPPRD,ERPPRDSTBY)'SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archive VALID_FOR=(ONLINE_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=ERPPRD';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ERPPRDSTBY LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=ERPPRDSTBY';
ALTER SYSTEM SET FAL_SERVER =ERPPRDSTBY SCOPE=BOTH;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = AUTO SCOPE = BOTH;
ALTER DATABASE FORCE LOGGING;

Create a new password file and copy it to the standby:

[oracle@ERPPrimary dbs]$
orapwd file=orapwERPPRD password=welcome1 entries=10 format=12 force=y


[oracle@ERPPrimary dbs]$ scp orapwERPPRD 129.150.123.167:$PWD/
orapwERPPRD                                                    





[oracle@ERPPrimary admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 21 18:54:28 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(ERPPRD,ERPPRDSTBY)'SCOPE=BOTH;

System altered.


SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archive VALID_FOR=(ONLINE_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=ERPPRD';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ERPPRDSTBY LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=ERPPRDSTBY';

System altered.

SQL> ALTER SYSTEM SET FAL_SERVER =ERPPRDSTBY SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = AUTO SCOPE = BOTH;

System altered.

SQL> ALTER DATABASE FORCE LOGGING;
ALTER DATABASE FORCE LOGGING
*
ERROR at line 1:
ORA-12920: database is already in force logging mode


SQL>


Verify the connections on Both Primary & Standby:


[oracle@ERPStandby dbs]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 21 19:20:53 2017

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

SQL> conn sys/welcome1@erpprdstby as sysdba
Connected.
SQL>


[oracle@ERPPrimary dbs]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 21 19:16:27 2017

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

SQL> conn sys/welcome1@erpprd

SQL> conn sys/welcome1@erpprd as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> exit



[oracle@ERPPrimary dbs]$ rman

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Nov 21 19:21:59 2017

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

RMAN> connect target /

connected to target database: ERPPRD (DBID=1847233764)

RMAN> connect auxiliary sys/welcome1@erpprdstby

connected to auxiliary database: ERPPRD (not mounted)




Start the duplicate database for standby :


[oracle@ERPPrimary ~]$ cat build.sh
rman<<EOF
connect target sys/welcome1@erpprd;
connect auxiliary sys/welcome1@erpprdstby;
set encryption on identified by 'welcome1';
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE DORECOVER
SPFILE
set control_files='/u02/app/oracle/oradata/ERPPRDSTBY/control01.ctl','/u03/app/oracle/fast_recovery_area/ERPPRDSTBY/control02.ctl'
SET db_unique_name='ERPPRDSTBY' COMMENT 'Is standby'
SET LOG_ARCHIVE_DEST_2='SERVICE=ERPPRD ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ERPPRD'
SET FAL_SERVER='ERPPRD' COMMENT 'Is primary'
set db_recovery_file_dest='/u01/app/'
set db_file_name_convert='/ERPPRD/','/ERPPRDSTBY/'
set log_file_name_convert='/ERPPRD/','/ERPPRDSTBY/'
NOFILENAMECHECK;
EOF



RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
2       B  A  A SBT_TAPE    21-NOV-17       1       1       YES        TAG20171121T153036
3       B  A  A SBT_TAPE    21-NOV-17       1       1       YES        TAG20171121T153036
4       B  A  A SBT_TAPE    21-NOV-17       1       1       YES        TAG20171121T153036
5       B  F  A SBT_TAPE    21-NOV-17       1       1       NO         TAG20171121T153044
6       B  F  A SBT_TAPE    21-NOV-17       1       1       NO         TAG20171121T153056
7       B  A  A *           21-NOV-17       1       2       NO         DBAAS_INCR_BACKUP
8       B  A  A *           21-NOV-17       1       2       NO         DBAAS_INCR_BACKUP
9       B  F  A *           21-NOV-17       1       2       NO         TAG20171121T153212





[oracle@ERPPrimary ~]$ ./build.sh

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Nov 22 14:33:59 2017

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

RMAN>
connected to target database: ERPPRD (DBID=1847233764)

RMAN>
connected to auxiliary database: ERPPRD (not mounted)

RMAN>
executing command: SET encryption
using target database control file instead of recovery catalog

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>
Starting Duplicate Db at 22-NOV-17
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=19 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: SID=256 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_SBT_TAPE_3
channel ORA_AUX_SBT_TAPE_3: SID=20 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_3: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_SBT_TAPE_4
channel ORA_AUX_SBT_TAPE_4: SID=257 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_4: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_SBT_TAPE_5
channel ORA_AUX_SBT_TAPE_5: SID=21 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_5: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=258 device type=DISK
current log archived

contents of Memory Script:
{
  backup as copy reuse
  targetfile  '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwERPPRD' auxiliary format
'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwERPPRDSTBY'   ;
  restore clone from service  'erpprd' spfile to
'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileERPPRDSTBY.ora';
  sql clone "alter system set spfile= ''/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileERPPRDSTBY.ora''";
}
executing Memory Script

Starting backup at 22-NOV-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
ignoring encryption for proxy or image copies
Finished backup at 22-NOV-17

Starting restore at 22-NOV-17
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
using channel ORA_AUX_SBT_TAPE_5
using channel ORA_AUX_DISK_1

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_1: restoring SPFILE
output file name=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileERPPRDSTBY.ora
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:01
Finished restore at 22-NOV-17

sql statement: alter system set spfile= ''/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileERPPRDSTBY.ora''

contents of Memory Script:
{
  sql clone "alter system set  control_files =
''/u02/app/oracle/oradata/ERPPRDSTBY/control01.ctl'', ''/u03/app/oracle/fast_recovery_area/ERPPRDSTBY/control02.ctl'' comment=
'''' scope=spfile";
  sql clone "alter system set  db_unique_name =
''ERPPRDSTBY'' comment=
''Is standby'' scope=spfile";
  sql clone "alter system set  LOG_ARCHIVE_DEST_2 =
''SERVICE=ERPPRD ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ERPPRD'' comment=
'''' scope=spfile";
  sql clone "alter system set  FAL_SERVER =
''ERPPRD'' comment=
''Is primary'' scope=spfile";
  sql clone "alter system set  db_recovery_file_dest =
''/u01/app/'' comment=
'''' scope=spfile";
  sql clone "alter system set  db_file_name_convert =
''/ERPPRD/'', ''/ERPPRDSTBY/'' comment=
'''' scope=spfile";
  sql clone "alter system set  log_file_name_convert =
''/ERPPRD/'', ''/ERPPRDSTBY/'' comment=
'''' scope=spfile";
  shutdown clone immediate;
  startup clone nomount;
}
executing Memory Script

sql statement: alter system set  control_files =  ''/u02/app/oracle/oradata/ERPPRDSTBY/control01.ctl'', ''/u03/app/oracle/fast_recovery_area/ERPPRDSTBY/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''ERPPRDSTBY'' comment= ''Is standby'' scope=spfile

sql statement: alter system set  LOG_ARCHIVE_DEST_2 =  ''SERVICE=ERPPRD ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ERPPRD'' comment= '''' scope=spfile

sql statement: alter system set  FAL_SERVER =  ''ERPPRD'' comment= ''Is primary'' scope=spfile

sql statement: alter system set  db_recovery_file_dest =  ''/u01/app/'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/ERPPRD/'', ''/ERPPRDSTBY/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/ERPPRD/'', ''/ERPPRDSTBY/'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2768240640 bytes

Fixed Size                     8796624 bytes
Variable Size                704644656 bytes
Database Buffers            1979711488 bytes
Redo Buffers                  75087872 bytes

contents of Memory Script:
{
  restore clone from service  'erpprd' standby controlfile;
}
executing Memory Script

Starting restore at 22-NOV-17
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=237 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: SID=19 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_SBT_TAPE_3
channel ORA_AUX_SBT_TAPE_3: SID=256 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_3: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_SBT_TAPE_4
channel ORA_AUX_SBT_TAPE_4: SID=20 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_4: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_SBT_TAPE_5
channel ORA_AUX_SBT_TAPE_5: SID=257 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_5: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_1: restoring control file
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:04
output file name=/u02/app/oracle/oradata/ERPPRDSTBY/control01.ctl
output file name=/u03/app/oracle/fast_recovery_area/ERPPRDSTBY/control02.ctl
Finished restore at 22-NOV-17

contents of Memory Script:
{
  sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
  set newname for tempfile  1 to
"/u04/app/oracle/oradata/temp/temp01.dbf";
  set newname for tempfile  2 to
"/u04/app/oracle/oradata/temp/pdbseed_temp012017-05-02_02-35-54-585-AM.dbf";
  set newname for tempfile  3 to
"/u04/app/oracle/oradata/temp/temp012017-05-02_02-35-54-585-AM.dbf";
  switch clone tempfile all;
  set newname for datafile  1 to
"/u02/app/oracle/oradata/ERPPRDSTBY/system01.dbf";
  set newname for datafile  3 to
"/u02/app/oracle/oradata/ERPPRDSTBY/sysaux01.dbf";
  set newname for datafile  4 to
"/u02/app/oracle/oradata/ERPPRDSTBY/undotbs01.dbf";
  set newname for datafile  5 to
"/u02/app/oracle/oradata/ERPPRDSTBY/pdbseed/system01.dbf";
  set newname for datafile  6 to
"/u02/app/oracle/oradata/ERPPRDSTBY/pdbseed/sysaux01.dbf";
  set newname for datafile  7 to
"/u02/app/oracle/oradata/ERPPRDSTBY/users01.dbf";
  set newname for datafile  8 to
"/u02/app/oracle/oradata/ERPPRDSTBY/pdbseed/undotbs01.dbf";
  set newname for datafile  9 to
"/u02/app/oracle/oradata/ERPPRDSTBY/PDB1/system01.dbf";
  set newname for datafile  10 to
"/u02/app/oracle/oradata/ERPPRDSTBY/PDB1/sysaux01.dbf";
  set newname for datafile  11 to
"/u02/app/oracle/oradata/ERPPRDSTBY/PDB1/undotbs01.dbf";
  set newname for datafile  12 to
"/u02/app/oracle/oradata/ERPPRDSTBY/PDB1/PDB1_users01.dbf";
  restore
  from  nonsparse   from service
'erpprd'   clone database
  ;
  sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u04/app/oracle/oradata/temp/temp01.dbf in control file
renamed tempfile 2 to /u04/app/oracle/oradata/temp/pdbseed_temp012017-05-02_02-35-54-585-AM.dbf in control file
renamed tempfile 3 to /u04/app/oracle/oradata/temp/temp012017-05-02_02-35-54-585-AM.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 22-NOV-17
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
using channel ORA_AUX_SBT_TAPE_5
using channel ORA_AUX_DISK_1

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00001 to /u02/app/oracle/oradata/ERPPRDSTBY/system01.dbf
channel ORA_AUX_SBT_TAPE_2: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_2: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_2: restoring datafile 00003 to /u02/app/oracle/oradata/ERPPRDSTBY/sysaux01.dbf
channel ORA_AUX_SBT_TAPE_3: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_3: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_3: restoring datafile 00004 to /u02/app/oracle/oradata/ERPPRDSTBY/undotbs01.dbf
channel ORA_AUX_SBT_TAPE_4: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_4: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_4: restoring datafile 00005 to /u02/app/oracle/oradata/ERPPRDSTBY/pdbseed/system01.dbf
channel ORA_AUX_SBT_TAPE_5: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_5: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_5: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_5: restoring datafile 00006 to /u02/app/oracle/oradata/ERPPRDSTBY/pdbseed/sysaux01.dbf
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service erpprd
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u02/app/oracle/oradata/ERPPRDSTBY/users01.dbf
channel ORA_AUX_SBT_TAPE_3: restore complete, elapsed time: 00:00:04
channel ORA_AUX_SBT_TAPE_3: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_3: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_3: restoring datafile 00008 to /u02/app/oracle/oradata/ERPPRDSTBY/pdbseed/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service erpprd
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u02/app/oracle/oradata/ERPPRDSTBY/PDB1/system01.dbf
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:22
channel ORA_AUX_SBT_TAPE_2: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_2: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_2: restoring datafile 00010 to /u02/app/oracle/oradata/ERPPRDSTBY/PDB1/sysaux01.dbf
channel ORA_AUX_SBT_TAPE_3: restore complete, elapsed time: 00:00:17
channel ORA_AUX_SBT_TAPE_3: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_3: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_3: restoring datafile 00011 to /u02/app/oracle/oradata/ERPPRDSTBY/PDB1/undotbs01.dbf
channel ORA_AUX_SBT_TAPE_4: restore complete, elapsed time: 00:00:21
channel ORA_AUX_SBT_TAPE_4: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_4: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_4: restoring datafile 00012 to /u02/app/oracle/oradata/ERPPRDSTBY/PDB1/PDB1_users01.dbf
channel ORA_AUX_SBT_TAPE_5: restore complete, elapsed time: 00:00:22
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:18
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:24
channel ORA_AUX_SBT_TAPE_4: restore complete, elapsed time: 00:00:04
channel ORA_AUX_SBT_TAPE_3: restore complete, elapsed time: 00:00:08
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:17
Finished restore at 22-NOV-17

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
  restore clone force from service  'erpprd'
          archivelog from scn  3011304;
  switch clone datafile all;
}
executing Memory Script

Starting restore at 22-NOV-17
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
using channel ORA_AUX_SBT_TAPE_5
using channel ORA_AUX_DISK_1

channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=25
channel ORA_AUX_SBT_TAPE_2: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_2: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_2: restoring archived log
archived log thread=1 sequence=26
channel ORA_AUX_SBT_TAPE_3: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_3: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_3: restoring archived log
archived log thread=1 sequence=27
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:01
channel ORA_AUX_SBT_TAPE_3: restore complete, elapsed time: 00:00:01
Finished restore at 22-NOV-17

datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=960734141 file name=/u02/app/oracle/oradata/ERPPRDSTBY/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=960734142 file name=/u02/app/oracle/oradata/ERPPRDSTBY/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=960734142 file name=/u02/app/oracle/oradata/ERPPRDSTBY/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=960734142 file name=/u02/app/oracle/oradata/ERPPRDSTBY/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=960734142 file name=/u02/app/oracle/oradata/ERPPRDSTBY/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=17 STAMP=960734142 file name=/u02/app/oracle/oradata/ERPPRDSTBY/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=18 STAMP=960734142 file name=/u02/app/oracle/oradata/ERPPRDSTBY/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=19 STAMP=960734142 file name=/u02/app/oracle/oradata/ERPPRDSTBY/PDB1/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=20 STAMP=960734142 file name=/u02/app/oracle/oradata/ERPPRDSTBY/PDB1/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=21 STAMP=960734142 file name=/u02/app/oracle/oradata/ERPPRDSTBY/PDB1/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=22 STAMP=960734143 file name=/u02/app/oracle/oradata/ERPPRDSTBY/PDB1/PDB1_users01.dbf

contents of Memory Script:
{
  set until scn  3011634;
  recover
  standby
  clone database
   delete archivelog
  ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 22-NOV-17
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
using channel ORA_AUX_SBT_TAPE_5
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch1_25_960650498.dbf
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch1_26_960650498.dbf
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch1_27_960650498.dbf
archived log file name=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch1_25_960650498.dbf thread=1 sequence=25
archived log file name=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch1_26_960650498.dbf thread=1 sequence=26
archived log file name=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch1_27_960650498.dbf thread=1 sequence=27
media recovery complete, elapsed time: 00:00:02
Finished recover at 22-NOV-17
Finished Duplicate Db at 22-NOV-17

RMAN>

Recovery Manager complete.

Check the status and database roles:



[oracle@ERPStandby dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 22 14:50:49 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


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

NAME      DB_UNIQUE_NAME       OPEN_MODE            DATABASE_ROLE
--------- -------------------- -------------------- ----------------
ERPPRD    ERPPRDSTBY           MOUNTED              PHYSICAL STANDBY




RMAN> select name,db_unique_name,open_mode,database_role from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
ERPPRD    ERPPRD                         READ WRITE           PRIMARY


Check the media recovery on Standby:




SQL> select process,sequence#,status from V$MANAGED_STANDBY;

PROCESS    SEQUENCE# STATUS
--------- ---------- ------------
DGRD               0 ALLOCATED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
DGRD               0 ALLOCATED
RFS                0 IDLE
RFS               30 RECEIVING

8 rows selected.

SQL> recover managed standby database using current logfile disconnect;


Media recovery complete.

SQL>
SQL> select process,sequence#,status from V$MANAGED_STANDBY;


PROCESS    SEQUENCE# STATUS
--------- ---------- ------------
DGRD               0 ALLOCATED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
DGRD               0 ALLOCATED
RFS                0 IDLE
RFS               30 RECEIVING
MRP0              30 WAIT_FOR_LOG

9 rows selected.


On Primary:

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive
Oldest online log sequence     28
Next log sequence to archive   30
Current log sequence           30


SQL> create table primary as select * from dba_users;

Table created.

SQL> select count(*) from primary;

 COUNT(*)
----------
       40

SQL> alter system archive log current;

System altered.

SQL>  archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive
Oldest online log sequence     29
Next log sequence to archive   31
Current log sequence           31


On Standby:
SQL> select process,sequence#,status from V$MANAGED_STANDBY;


PROCESS    SEQUENCE# STATUS
--------- ---------- ------------
DGRD               0 ALLOCATED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
DGRD               0 ALLOCATED
RFS                0 IDLE
RFS               32 RECEIVING
MRP0              32 WAIT_FOR_LOG

9 rows selected.



SQL> create table secondary as select * from dba_directories;

Table created.

SQL> select count(*) from secondary;

 COUNT(*)
----------
       12

SQL>


Manually Fail Over to the Standby Database Without Flashback Technology Enabled

Failover is the process of activating the standby database in case the production (primary) database is deformed so as to minimize the risk of data loss. It is not reversible. When the failover process is enabled, the standby database is switched to function as the primary database and you would have to re-create the standby database.
In a worst-case situation when the Data Guard primary database is not available for production, you can activate the standby database to be the primary production database by performing the following steps.
  1. From the secondary database, run the following SQL statements:
  2. recover managed standby database cancel;
  3. recover managed standby database finish;
  4. alter database activate standby database;
  5. startup force




SQL> recover managed standby database cancel;
Media recovery complete.

SQL> recover managed standby database finish;
Media recovery complete.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from primary;

 COUNT(*)
----------
       40




SQL> alter database activate standby database;

Database altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 2768240640 bytes
Fixed Size                  8796624 bytes
Variable Size             704644656 bytes
Database Buffers         1979711488 bytes
Redo Buffers               75087872 bytes
Database mounted.
Database opened.


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

NAME      DB_UNIQUE_NAME       OPEN_MODE            DATABASE_ROLE
--------- -------------------- -------------------- ----------------
ERPPRD    ERPPRDSTBY           READ WRITE           PRIMARY



Rebuild the Primary Database:

Because we don't have flashback technology enabled on both sides, manually re-create the old primary database as the physical standby database from the scratch.


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

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
ERPPRD    ERPPRD                         READ WRITE           PRIMARY




[oracle@ERPPrimary ~]$ rman

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Nov 22 15:54:50 2017

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

RMAN> connect target sys/welcome1@erpprdstby;

connected to target database: ERPPRD (DBID=1847233764)

RMAN> connect auxiliary sys/welcome1@erpprd;

connected to auxiliary database: ERPPRD (not mounted)

RMAN> set encryption on identified by 'welcome1';


executing command: SET encryption
using target database control file instead of recovery catalog

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

Starting Duplicate Db at 22-NOV-17
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=255 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: SID=19 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_SBT_TAPE_3
channel ORA_AUX_SBT_TAPE_3: SID=256 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_3: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_SBT_TAPE_4
channel ORA_AUX_SBT_TAPE_4: SID=20 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_4: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_SBT_TAPE_5
channel ORA_AUX_SBT_TAPE_5: SID=257 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_5: Oracle Database Backup Service Library VER=3.17.9.5
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK

contents of Memory Script:
{
  backup as copy reuse
  targetfile  '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwERPPRDSTBY' auxiliary format
'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwERPPRD'   ;
}
executing Memory Script

Starting backup at 22-NOV-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
ignoring encryption for proxy or image copies
Finished backup at 22-NOV-17

contents of Memory Script:
{
  restore clone from service  'erpprdstby' standby controlfile;
}
executing Memory Script

Starting restore at 22-NOV-17
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
using channel ORA_AUX_SBT_TAPE_5
using channel ORA_AUX_DISK_1

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: using network backup set from service erpprdstby
channel ORA_AUX_SBT_TAPE_1: restoring control file
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:03
output file name=/u02/app/oracle/oradata/ERPPRD/control01.ctl
output file name=/u03/app/oracle/fast_recovery_area/ERPPRD/control02.ctl
Finished restore at 22-NOV-17

contents of Memory Script:
{
  sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
Using previous duplicated file /u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_system_f1c74bgv_.dbf for datafile 1 with checkpoint SCN of 3021330
Using previous duplicated file /u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_sysaux_f1c74cch_.dbf for datafile 3 with checkpoint SCN of 3021331
Using previous duplicated file /u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_undotbs1_f1c74cr3_.dbf for datafile 4 with checkpoint SCN of 3021338
Using previous duplicated file /u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_users_f1c74fr6_.dbf for datafile 7 with checkpoint SCN of 3021342
Using previous duplicated file /u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_system_f1c74js9_.dbf for datafile 9 with checkpoint SCN of 3021349
Using previous duplicated file /u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_sysaux_f1c74rfk_.dbf for datafile 10 with checkpoint SCN of 3021366
Using previous duplicated file /u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_undotbs1_f1c74sjp_.dbf for datafile 11 with checkpoint SCN of 3021368
Using previous duplicated file /u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_users_f1c74tnf_.dbf for datafile 12 with checkpoint SCN of 3021371

contents of Memory Script:
{
  set newname for clone tempfile  1 to new;
  set newname for clone tempfile  2 to new;
  set newname for clone tempfile  3 to new;
  switch clone tempfile all;
  set newname for datafile  1 to
"/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_system_f1c74bgv_.dbf";
  set newname for datafile  3 to
"/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_sysaux_f1c74cch_.dbf";
  set newname for datafile  4 to
"/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_undotbs1_f1c74cr3_.dbf";
  set newname for clone datafile  5 to new;
  set newname for clone datafile  6 to new;
  set newname for datafile  7 to
"/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_users_f1c74fr6_.dbf";
  set newname for clone datafile  8 to new;
  set newname for datafile  9 to
"/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_system_f1c74js9_.dbf";
  set newname for datafile  10 to
"/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_sysaux_f1c74rfk_.dbf";
  set newname for datafile  11 to
"/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_undotbs1_f1c74sjp_.dbf";
  set newname for datafile  12 to
"/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_users_f1c74tnf_.dbf";
  restore
  from  nonsparse   from service
'erpprdstby'   clone datafile
   5, 6, 8   ;
  sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 2 to /u02/app/oracle/oradata/ERPPRD/4E82661F3A3E1950E053A243C40A522B/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_temp_%u_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 22-NOV-17
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
using channel ORA_AUX_SBT_TAPE_5
using channel ORA_AUX_DISK_1

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: using network backup set from service erpprdstby
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00005 to /u02/app/oracle/oradata/ERPPRD/4E82661F3A3E1950E053A243C40A522B/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_2: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_2: using network backup set from service erpprdstby
channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_2: restoring datafile 00006 to /u02/app/oracle/oradata/ERPPRD/4E82661F3A3E1950E053A243C40A522B/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_3: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_3: using network backup set from service erpprdstby
channel ORA_AUX_SBT_TAPE_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_3: restoring datafile 00008 to /u02/app/oracle/oradata/ERPPRD/4E82661F3A3E1950E053A243C40A522B/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:09
channel ORA_AUX_SBT_TAPE_3: restore complete, elapsed time: 00:00:08
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:17
Finished restore at 22-NOV-17

sql statement: alter system archive log current

contents of Memory Script:
{
  restore clone force from service  'erpprdstby'
          archivelog from scn  3021330;
  catalog clone datafilecopy  "/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_system_f1c74bgv_.dbf",
"/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_sysaux_f1c74cch_.dbf",
"/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_undotbs1_f1c74cr3_.dbf",
"/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_users_f1c74fr6_.dbf",
"/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_system_f1c74js9_.dbf",
"/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_sysaux_f1c74rfk_.dbf",
"/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_undotbs1_f1c74sjp_.dbf",
"/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_users_f1c74tnf_.dbf";
  switch clone datafile  1 to datafilecopy
"/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_system_f1c74bgv_.dbf";
  switch clone datafile  3 to datafilecopy
"/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_sysaux_f1c74cch_.dbf";
  switch clone datafile  4 to datafilecopy
"/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_undotbs1_f1c74cr3_.dbf";
  switch clone datafile  7 to datafilecopy
"/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_users_f1c74fr6_.dbf";
  switch clone datafile  9 to datafilecopy
"/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_system_f1c74js9_.dbf";
  switch clone datafile  10 to datafilecopy
"/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_sysaux_f1c74rfk_.dbf";
  switch clone datafile  11 to datafilecopy
"/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_undotbs1_f1c74sjp_.dbf";
  switch clone datafile  12 to datafilecopy
"/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_users_f1c74tnf_.dbf";
  switch clone datafile all;
}
executing Memory Script

Starting restore at 22-NOV-17
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
using channel ORA_AUX_SBT_TAPE_5
using channel ORA_AUX_DISK_1

channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: using network backup set from service erpprdstby
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=2
channel ORA_AUX_SBT_TAPE_2: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_2: using network backup set from service erpprdstby
channel ORA_AUX_SBT_TAPE_2: restoring archived log
archived log thread=1 sequence=3
channel ORA_AUX_SBT_TAPE_3: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_3: using network backup set from service erpprdstby
channel ORA_AUX_SBT_TAPE_3: restoring archived log
archived log thread=1 sequence=4
channel ORA_AUX_SBT_TAPE_4: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_4: using network backup set from service erpprdstby
channel ORA_AUX_SBT_TAPE_4: restoring archived log
archived log thread=1 sequence=5
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:02
channel ORA_AUX_SBT_TAPE_3: restore complete, elapsed time: 00:00:01
channel ORA_AUX_SBT_TAPE_4: restore complete, elapsed time: 00:00:02
Finished restore at 22-NOV-17

cataloged datafile copy
datafile copy file name=/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_system_f1c74bgv_.dbf RECID=26 STAMP=960738968
cataloged datafile copy
datafile copy file name=/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_sysaux_f1c74cch_.dbf RECID=27 STAMP=960738968
cataloged datafile copy
datafile copy file name=/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_undotbs1_f1c74cr3_.dbf RECID=28 STAMP=960738969
cataloged datafile copy
datafile copy file name=/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_users_f1c74fr6_.dbf RECID=29 STAMP=960738969
cataloged datafile copy
datafile copy file name=/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_system_f1c74js9_.dbf RECID=30 STAMP=960738969
cataloged datafile copy
datafile copy file name=/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_sysaux_f1c74rfk_.dbf RECID=31 STAMP=960738969
cataloged datafile copy
datafile copy file name=/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_undotbs1_f1c74sjp_.dbf RECID=32 STAMP=960738969
cataloged datafile copy
datafile copy file name=/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_users_f1c74tnf_.dbf RECID=33 STAMP=960738969

datafile 1 switched to datafile copy
input datafile copy RECID=26 STAMP=960738968 file name=/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_system_f1c74bgv_.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=27 STAMP=960738968 file name=/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_sysaux_f1c74cch_.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=28 STAMP=960738969 file name=/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_undotbs1_f1c74cr3_.dbf

datafile 7 switched to datafile copy
input datafile copy RECID=29 STAMP=960738969 file name=/u02/app/oracle/oradata/ERPPRD/datafile/o1_mf_users_f1c74fr6_.dbf

datafile 9 switched to datafile copy
input datafile copy RECID=30 STAMP=960738969 file name=/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_system_f1c74js9_.dbf

datafile 10 switched to datafile copy
input datafile copy RECID=31 STAMP=960738969 file name=/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_sysaux_f1c74rfk_.dbf

datafile 11 switched to datafile copy
input datafile copy RECID=32 STAMP=960738969 file name=/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_undotbs1_f1c74sjp_.dbf

datafile 12 switched to datafile copy
input datafile copy RECID=33 STAMP=960738969 file name=/u02/app/oracle/oradata/ERPPRD/4E82B9D2D3A83428E053A243C40A3A74/datafile/o1_mf_users_f1c74tnf_.dbf

datafile 5 switched to datafile copy
input datafile copy RECID=34 STAMP=960738972 file name=/u02/app/oracle/oradata/ERPPRD/4E82661F3A3E1950E053A243C40A522B/datafile/o1_mf_system_f1c7j3th_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=35 STAMP=960738972 file name=/u02/app/oracle/oradata/ERPPRD/4E82661F3A3E1950E053A243C40A522B/datafile/o1_mf_sysaux_f1c7j4fh_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=36 STAMP=960738972 file name=/u02/app/oracle/oradata/ERPPRD/4E82661F3A3E1950E053A243C40A522B/datafile/o1_mf_undotbs1_f1c7j546_.dbf
Finished Duplicate Db at 22-NOV-17

RMAN>



oracle@ERPPrimary ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 22 16:03:11 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
ERPPRD    ERPPRD                         MOUNTED              PHYSICAL STANDBY



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

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
ERPPRD    ERPPRDSTBY                     READ WRITE           PRIMARY

Create standby logfiles:


SQL> select member,type from v$logfile;

MEMBER                                        TYPE
--------------------------------------------- -------
/u04/app/oracle/redo/redo03.log               ONLINE
/u04/app/oracle/redo/redo02.log               ONLINE
/u04/app/oracle/redo/redo01.log               ONLINE
/u01/app/oracle/product/12.2.0/dbhome_1/dbs/a STANDBY
rch1_0_960650498.dbf




SQL> show parameter create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u02/app/oracle/oradata
SQL> alter database add standby logfile;

Database altered.



SQL> alter database add standby logfile;

Database altered.

SQL> select member,type from v$logfile;

MEMBER                                                                           TYPE
-------------------------------------------------------------------------------- -------
/u04/app/oracle/redo/redo03.log                                                  ONLINE
/u04/app/oracle/redo/redo02.log                                                  ONLINE
/u04/app/oracle/redo/redo01.log                                                  ONLINE
/u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch1_0_960650498.dbf                STANDBY
/u02/app/oracle/oradata/ERPPRDSTBY/onlinelog/o1_mf_5_f1c8dpng_.log               STANDBY
/u01/app/ERPPRDSTBY/onlinelog/o1_mf_5_f1c8dxvk_.log                              STANDBY
/u02/app/oracle/oradata/ERPPRDSTBY/onlinelog/o1_mf_6_f1c8fs8d_.log               STANDBY

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database recover managed standby database disconnect nodelay;

Database altered.



SQL>
SQL> select count(*) from primary;
select count(*) from primary
                    *
ERROR at line 1:
ORA-00942: table or view does not exist





SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
ERPPRD                         MOUNTED              PHYSICAL STANDBY

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select db_unique_name,open_mode,database_role from v$database;


DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
ERPPRD                         READ ONLY            PHYSICAL STANDBY

SQL> SQL> alter database recover managed standby database disconnect nodelay;

Database altered.

SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
ERPPRD                         READ ONLY WITH APPLY PHYSICAL STANDBY



SQL> alter database open read only;

Database altered.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select process,sequence#,status from V$MANAGED_STANDBY;

PROCESS    SEQUENCE# STATUS
--------- ---------- ------------
DGRD               0 ALLOCATED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
ARCH               0 CONNECTED
DGRD               0 ALLOCATED
RFS                7 RECEIVING
RFS                0 IDLE
MRP0               7 WAIT_FOR_LOG

9 rows selected.

SQL> select count(*) from primary;

 COUNT(*)
----------
       40

SQL> select count(*) from secondary;
select count(*) from secondary
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(*) from third;

 COUNT(*)
----------
       40





SQL> create table test (a number);

Table created.


SQL> begin
 2  for i in 1..100 loop
 3  insert into test values(i);
 4  end loop;
 5  end;
 6  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.




Enable flashback on Primary and Standby:


  1. On the primary machine, mount the database, configure flashback retention, start the Oracle Flashback Database capability, and open the database by running the following statements:
  2. SHUTDOWN IMMEDIATE;
  3. STARTUP MOUNT;
  4. ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=2g scope=both;
  5. ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/app/' scope=both;
  6. ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=240;
  7. ALTER DATABASE FLASHBACK ON;
  8. ALTER DATABASE OPEN;



NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u03/app/oracle/fast_recovery_
                                                area
db_recovery_file_dest_size           big integer 76G
recovery_parallelism                 integer     0
remote_recovery_file_dest            string

db_flashback_retention_target        integer     1440
undo_retention                       integer     900


SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback on;

Database altered.


SQL> set linesize 1000
SQL> select db_unique_name,name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_NAME                 NAME      OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
------------------------------ --------- -------------------- ---------------- ------------------
ERPPRDSTBY                     ERPPRD    READ WRITE           PRIMARY          YES




Do the same on the standby:


  1. On the standby machine, cancel the recovery, mount the database, configure flashback retention, start the Oracle Flashback Database capability, open the database in read-only mode, and start the recovery process by running the following statements:
  2. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
  3. STARTUP FORCE MOUNT;
  4. alter system set DB_RECOVERY_FILE_DEST_SIZE=2g scope=both;
  5. alter system set db_recovery_file_dest='/u01/app/' scope=both;
  6. ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=240;
  7. ALTER SYSTEM FLASHBACK ON;
  8. ALTER DATABASE OPEN READ ONLY;
  9. recover managed standby database using current logfile disconnect from session;



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

Total System Global Area 2768240640 bytes
Fixed Size                  8796624 bytes
Variable Size             704644656 bytes
Database Buffers         1979711488 bytes
Redo Buffers               75087872 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES


SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database disconnect nodelay;

Database altered.

QL> select db_unique_name,name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_NAME                 NAME      OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
------------------------------ --------- -------------------- ---------------- ------------------
ERPPRD                         ERPPRD    READ ONLY            PHYSICAL STANDBY YES



Setup the DG Broker:

ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/12.2.0
                                                /dbhome_1/dbs/dr1ERPPRDSTBY.da
                                                t
dg_broker_config_file2               string      /u01/app/oracle/product/12.2.0
                                                /dbhome_1/dbs/dr2ERPPRDSTBY.da
                                                t
dg_broker_start                      boolean     FALSE


SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

System altered.

Ensure that the listener.ora file on both servers has a static entry for the DGMGRL command-line interface.

On the primary server, create the Data Guard broker configuration by running the statements:


[oracle@ERPStandby ~]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Wed Nov 22 19:43:09 2017

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/welcome1
Connected to "ERPPRDSTBY"
Connected as SYSDG.
DGMGRL> create configuration 'erpprd-cloud' as primary  database is 'ERPPRDSTBY' connect identifier is 'ERPPRDSTBY';
Configuration "erpprd-cloud" created with primary database "ERPPRDSTBY"

DGMGRL> show configuration;

Configuration - erpprd-cloud

 Protection Mode: MaxPerformance
 Members:
 ERPPRDSTBY - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> add database 'ERPPRD' as connect identifier is 'ERPPRD';
Database "ERPPRD" added


DGMGRL> show configuration

Configuration - erpprd-cloud

 Protection Mode: MaxPerformance
 Members:
 ERPPRDSTBY - Primary database
   ERPPRD     - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED


DGMGRL> enable configuration
Enabled.
DGMGRL>






DGMGRL> edit database 'ERPPRD' set property ArchiveLagTarget=3600;
Property "archivelagtarget" updated
DGMGRL> edit database 'ERPPRD' set property  LogArchiveMinSucceedDest=1;
Property "logarchiveminsucceeddest" updated
DGMGRL> edit database 'ERPPRD' set property  LogArchiveMaxProcesses=4;
Property "logarchivemaxprocesses" updated
DGMGRL> edit database 'ERPPRDSTBY'  set property ArchiveLagTarget=3600;
Property "archivelagtarget" updated
DGMGRL> edit database 'ERPPRDSTBY'  set property  LogArchiveMinSucceedDest=1;
Property "logarchiveminsucceeddest" updated
DGMGRL> edit database 'ERPPRDSTBY'  set property   LogArchiveMaxProcesses=4;
Property "logarchivemaxprocesses" updated






DGMGRL> failover to 'ERPPRD';
Performing failover NOW, please wait...
Failover succeeded, new primary is "ERPPRD"
DGMGRL>



DGMGRL> reinstate database 'ERPPRDSTBY';
Reinstating database "ERPPRDSTBY", please wait...
SUCCESS


DGMGRL> show configuration

Configuration - erpprd-cloud

 Protection Mode: MaxPerformance
 Members:
 ERPPRD     - Primary database
   ERPPRDSTBY - Physical standby database (disabled)
     ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 102 seconds ago)



























ErrorS:


1)

SQL> conn sys/welcome1@erpprd as sysdba
Connected.

SQL> show user
USER is "SYS"

contents of Memory Script:
{
  backup as copy reuse
  targetfile  '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwERPPRD' auxiliary format
'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwERPPRDSTBY'   ;
  restore clone from service  'erpprd' spfile to
'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileERPPRDSTBY.ora';
  sql clone "alter system set spfile= ''/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileERPPRDSTBY.ora''";
}
executing Memory Script

Starting backup at 21-NOV-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=276 device type=DISK
ignoring encryption for proxy or image copies
Finished backup at 21-NOV-17

Starting restore at 21-NOV-17
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
using channel ORA_AUX_SBT_TAPE_5
using channel ORA_AUX_DISK_1

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: using network backup set from service erpprd
channel ORA_AUX_SBT_TAPE_1: restoring SPFILE
output file name=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileERPPRDSTBY.ora
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/21/2017 19:35:48
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-19849: error while reading backup piece from service erpprd
ORA-19913: unable to decrypt backup
ORA-19660: some files in the backup set could not be verified
ORA-19685: SPFILE could not be verified
ORA-19849: error while reading backup piece from service erpprd
ORA-19913: unable to decrypt backup

RMAN>





RMAN> show all
2> ;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ERPPRD are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 5 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' MAXPIECESIZE 2 G PARMS  'SBT_LIBRARY=libopc.so, ENV=(OPC_PFILE=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/opcERPPRD.ora)';
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/snapcf_ERPPRD.f'; # default



PC_HOST=https://salescloud44.storage.oraclecloud.com/v1/Storage-salescloud44
OPC_WALLET='LOCATION=file:/u01/app/oracle/admin/ERPPRD/opc_wallet CREDENTIAL_ALIAS=alias_opc'

OPC_CONTAINER=db_failover

_OPC_DEFERRED_DELETE=true
~

Fix:
[oracle@ERPPrimary ~]$ cat build.sh
rman<<EOF
connect target sys/welcome1@erpprd;
connect auxiliary sys/welcome1@erpprdstby;
set encryption on identified by 'welcome1';
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE DORECOVER
SPFILE
set control_files='/u02/app/oracle/oradata/ERPPRDSTBY/control01.ctl','/u03/app/oracle/fast_recovery_area/ERPPRDSTBY/control02.ctl'
SET db_unique_name='ERPPRDSTBY' COMMENT 'Is standby'
SET LOG_ARCHIVE_DEST_2='SERVICE=ERPPRD ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ERPPRD'
SET FAL_SERVER='ERPPRD' COMMENT 'Is primary'
set db_recovery_file_dest='/u01/app/'
set db_file_name_convert='/ERPPRD/','/ERPPRDSTBY/'
set log_file_name_convert='/ERPPRD/','/ERPPRDSTBY/'
NOFILENAMECHECK;
EOF


2)
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
ALTER DATABASE RECOVER  managed standby database using current logfile disconnect
2017-11-22T16:13:49.155306+00:00

Fix:-
SQL> alter database recover managed standby database disconnect nodelay;


3)DGMGRL> add database 'ERPPRD' as connect identifier is 'ERPPRD';
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.

Fix:
On Standby:
SQL> alter system set LOG_ARCHIVE_DEST_2='' scope=both;

System altered.