Cloud Control 13c Release 3 Database Template

Got excited to find:
Creating a Database Instance with Preconfigured Repository Using Database Templates

Templates can be download at:
Database Template (with EM 13.3.0.0 repository pre-configured) for Installing Oracle Enterprise Manager Cloud Control 13c Release 3 (13.3.0.0)

Got disappointed to find template is only available for 12.1.0.2.0 DB and 18.1.0.0.0 DB.

Creating a Database Instance with Preconfigured Repository Using Database Templates Caution:

Make sure that the 18.1.0.0.0_Database_Template_for_EM13_3_0_0_0_.zip file is used only for Enterprise Manager Cloud Control 13c Release 3.

It looks like the template version must match exactly from an example below.

EM 13c: Creating an Enterprise Manager 13c Cloud Control Repository using a Database Template Fails: ORA-00603: ORACLE server session terminated by fatal error (Doc ID 2291220.1)	

While the 12.2.0.1 version of the database is supported for EM 13.2 repository use, a database template for the EM 13.2 repository database is only supplied for the 12.1.0.2 version of the database, and they are specific for the version:

Oracle skipped 12.2 DB version when creating DB template which is disappointing.

Does database really need to be on 12.2?

Advertisements

To or To Not Specify Thread# When Creating SRL?

Just quick and dirty notes on some references.

Notice that the Thread # is 0 for the SRL’s. Resist the temptation to explicitly assign the SRL to a specific thread. If you are using Oracle RAC, a SRL with with Thread # of 0 can be used by any primary instance, any thread from the primary.
https://community.oracle.com/docs/DOC-1007036

Warning: standby redo logs not configured for thread <n> on <db_unique_name> (Doc ID 1956103.1)
Bug 14674237 – VALIDATE DATABASE from BROKER incorrectly reports on standby redo log files (Doc ID 14674237.8)

Warning: standby redo logs not configured for thread 0 on DATABASE
https://jameshuangsj.wordpress.com/2018/01/17/warning-standby-redo-logs-not-configured-for-thread-0-on-database/
When run “Validate Database” DGML command, get “Warning: standby redo logs not configured for thread 0 ” and “Insufficient SRLs” warnings on 12.1.0.2 database with “Database Patch Set Update : 12.1.0.2.170814 (26609783)”.

Oracle 12c DataGuard – Insufficient SRLs reported by DGMGRL VALIDATE DATABASE VERBOSE
https://blog.dbi-services.com/oracle-12c-dataguard-insufficient-srls-reported-by-dgmgrl-validate-database-verbose/
Even on a single instance, use the thread number in your create Standby Redo Log statement.

Troubleshooting GoldenGate OGG-00303: Unable to open credential store. Error code 43,490

After applying Oracle GoldenGate V12.2.0.1.170919 for Oracle Database 12c OPTIMIZER Patch for Bug# 26849949, starting GoldenGate extract failed with OGG-00303: Unable to open credential store. Error code 43,490.

Here is what the report looks like.

$ head -50 E_LAX6.rpt
***********************************************************************
                 Oracle GoldenGate Capture for Oracle
 Version 12.2.0.1.170919 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_171030.0908_FBO
   Linux, x64, 64bit (optimized), Oracle 12c on Oct 30 2017 20:59:41
 
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2018-10-25 15:08:33
***********************************************************************

Operating System Version:
Linux
Version #2 SMP Wed Jul 11 12:11:36 PDT 2018, Release 4.1.12-94.8.5.el7uek.x86_64
Node: localhost
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 23154

Description: 

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2018-10-25 15:08:33  INFO    OGG-03059  Operating system character set identified as UTF-8.

2018-10-25 15:08:33  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.
EXTRACT e_lax
USERIDALIAS gguser

Source Context :
  SourceModule            : [er.init]
  SourceID                : [/scratch/aime/adestore/views/aime_adc4150431/oggcore/OpenSys/src/app/er/init.cpp]
  SourceFunction          : [get_infile_params]
  SourceLine              : [5554]
  ThreadBacktrace         : [11] elements
                          : [/u01/gg/12.2.0/libgglog.so(CMessageContext::AddThreadContext()+0x1b) [0x7f714024709b]]
                          : [/u01/gg/12.2.0/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x135) [0x7f7140241165]]
                          : [/u01/gg/12.2.0/libgglog.so(_MSG_ERR_STARTUP_PARAMERROR_ERRORTEXT(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x30) [0x7f71402308d0]]
                          : [/u01/gg/12.2.0/extract(get_infile_params(time_elt_def*, time_elt_def*, char**, ggs::gglib::ggdatasource::DataSourceParams&, ggs::Heartbeat::MapGeneratorParams&)+0x5da1) [0x5c4c91]]
                          : [/u01/gg/12.2.0/extract() [0x5f036a]]
                          : [/u01/gg/12.2.0/extract(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x60) [0x6cea60]]
                          : [/u01/gg/12.2.0/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)+0x14d) [0x6cfcdd]]

 

Since I did not have the password for database gguser, I modified the password from the database and recreated credentialstore using ggsci.

info credentialstore
delete credentialstore
create wallet.
add credentialstore
alter credentialstore add user gguser alias gguser.
alter credentialstore add user GGUSER alias GGUSER
info credentialstore

Reason check credentialstore before deleting is to determine aliases.

GGSCI 1> info credentialstore

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: gguser
  Userid: gguser

  Alias: GGUSER
  Userid: GGUSER

Here are the steps from ggserr.log.

2018-10-25 15:44:00  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): create wallet.
2018-10-25 15:44:00  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Created wallet at location 'dirwlt'.
2018-10-25 15:44:00  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Opened wallet at location 'dirwlt'.
2018-10-25 15:44:10  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): add credentialstore.
2018-10-25 15:44:10  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Credential store created in ./dircrd/.
2018-10-25 15:44:22  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): alter credentialstore add user gguser alias gguser.
2018-10-25 15:44:31  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Credential store in ./dircrd/ altered.
2018-10-25 15:44:55  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): alter credentialstore add user GGUSER alias GGUSER.
2018-10-25 15:45:06  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Credential store in ./dircrd/ altered.
2018-10-25 15:45:15  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): info credentialstore.
2018-10-25 15:45:15  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Reading from ./dircrd/:.

Here are the permissions for the files.

$ chmod 775 -R dirwlt/ dircrd/
$ ls -l dirwlt/ dircrd/
dircrd/:
total 4
-rwxrwxr-x 1 gguser oinstall 701 Oct 25 15:45 cwallet.sso

dirwlt/:
total 4
-rwxrwxr-x 1 gguser oinstall 290 Oct 25 15:44 cwallet.sso

Starting extract failed again!

2018-10-25 15:45:30  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (gguser): dblogin useridalias gguser.
2018-10-25 15:45:35  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (gguser): start e*.
2018-10-25 15:45:35  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host 10.80.27.191:39060 (START EXTRACT E_LAX ).
2018-10-25 15:45:35  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #5).
2018-10-25 15:45:35  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT E_LAX starting.
2018-10-25 15:45:35  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, e_lax.prm:  EXTRACT E_LAX starting.
2018-10-25 15:45:35  INFO    OGG-03059  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Operating system character set identified as UTF-8.
2018-10-25 15:45:35  INFO    OGG-02695  Oracle GoldenGate Capture for Oracle, e_lax.prm:  ANSI SQL parameter syntax is used for parameter parsing.
2018-10-25 15:45:35  ERROR   OGG-00303  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Unable to open credential store. Error code 43,490.
2018-10-25 15:45:35  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, e_lax.prm:  PROCESS ABENDING.

Set environment variables for ORACLE_HOME and ORACLE_SID for extract, and restart extract solved the issue.

FYI – environment variables already exist from OS.

2018-10-25 15:45:38  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (gguser): info all.
2018-10-25 15:52:44  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): start e*.
2018-10-25 15:52:44  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host 10.80.27.191:39169 (START EXTRACT E_LAX ).
2018-10-25 15:52:44  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #5).
2018-10-25 15:52:44  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT E_LAX starting.
2018-10-25 15:52:44  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, e_lax.prm:  EXTRACT E_LAX starting.
2018-10-25 15:52:44  INFO    OGG-03059  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Operating system character set identified as UTF-8.
2018-10-25 15:52:44  INFO    OGG-02695  Oracle GoldenGate Capture for Oracle, e_lax.prm:  ANSI SQL parameter syntax is used for parameter parsing.
2018-10-25 15:52:44  INFO    OGG-02095  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_2.
2018-10-25 15:52:44  INFO    OGG-02095  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Successfully set environment variable ORACLE_SID=sourcedb.
2018-10-25 15:52:44  INFO    OGG-02095  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_2.
2018-10-25 15:52:44  INFO    OGG-02095  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Successfully set environment variable ORACLE_SID=sourcedb.
2018-10-25 15:52:56  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, e_lax.prm:  EXTRACT E_LAX started.

Currently, I don’t know if patching caused the issue or if it is a pre-existing condition.

What’s interesting is the same issue happened for another system where environment variables for ORACLE_HOME and ORACLE_SID were not set for extract.

Instead of restarting just the extract, all the processes were stopped, and restarted.

It would be a good idea to stop and start Goldengate processes before patching in order to identify any pre-existing conditions.

12.1.0.2.0 ORA-01033: ORACLE initialization or shutdown in progress cascade standby

Objective is to create RAC cascade standby (olapdr) from existing standby (oltpdr) on the same host.

Cascade Standby: ORACLE_SID=olap1; db_name=oltp; db_unique_name=olapdr
Standby:         ORACLE_SID=oltp1; db_name=oltp; db_unique_name=oltpdr

 

Configuration for standby (oltpdr)

$ srvctl config database -d oltpdr
Spfile: +DATA/OLTPDR/spfileoltpdr.ora
Password file: +DATA/OLTPDR/orapwoltpdr

Copy existing password file from disk to ASM for cascade standby (olapdr)

This did not work and possibly different from password file at ASM.
Please don’t ask me why.

-rw-r----- 1 oracle oinstall 7680 Sep 18 13:28 orapwolap
-rw-r----- 1 oracle oinstall 7680 Sep 18 13:28 orapwolap1
-rw-r----- 1 oracle oinstall 7680 May 28 12:08 orapwoltp
-rw-r----- 1 oracle oinstall 7680 May 28 12:08 orapwoltp1

$ cp $ORACLE_HOME/dbs/orapwolap /tmp/orapwolapdr
ASMCMD> pwcopy /tmp/orapwolapdr +DATA/OLAPDR/orapwolapdr

Check standby (oltpdr)

oltp1> @dataguard.sql

Session altered.

*** v$database ***

DB              OPEN                   DATABASE                                REMOTE     SWITCHOVER         DATAGUARD  PRIMARY_DB
UNIQUE_NAME     MODE                   ROLE               PROTECTION_MODE      ARCHIVE    STATUS             BROKER     UNIQUE_NAME
--------------- ---------------------- ------------------ -------------------- ---------- ------------------ ---------- ---------------
oltpdr          READ ONLY WITH APPLY   PHYSICAL STANDBY   MAXIMUM PERFORMANCE  ENABLED    NOT ALLOWED        ENABLED    oltp

*** gv$archive_dest_status ***
                             DB                                        DATABASE                     RECOVERY
 INST  DEST TARGET           UNIQUE_NAME     DESTINATION               MODE            STATUS       MODE                    SCHEDULE PROCESS
----- ----- ---------------- --------------- ------------------------- --------------- ------------ ----------------------- -------- --------
    1     1 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST OPEN_READ-ONLY  VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
          5 REMOTE           olapdr          olapdr                    UNKNOWN         ERROR        IDLE                    ACTIVE   ARCH
         32 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST UNKNOWN         VALID        IDLE                    ACTIVE   RFS

    2     1 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST OPEN_READ-ONLY  VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
          5 REMOTE           olapdr          olapdr                    UNKNOWN         ERROR        IDLE                    ACTIVE   ARCH
         32 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST UNKNOWN         VALID        IDLE                    ACTIVE   RFS

6 rows selected.

 INST  DEST STATUS       SRL GAP_STATUS      ERROR
----- ----- ------------ --- --------------- --------------------------------------------------------------------------------
    1     1 VALID        NO                  NONE
          5 ERROR        NO  RESOLVABLE GAP  ORA-01033: ORACLE initialization or shutdown in progress
         32 VALID        NO                  NONE

    2     1 VALID        NO                  NONE
          5 ERROR        NO  RESOLVABLE GAP  ORA-01033: ORACLE initialization or shutdown in progress
         32 VALID        NO                  NONE

6 rows selected.

Suggestion from teammate is to copy password file at ASM from oltpdr to olapdr

ASMCMD> pwcopy +DATA/OLTPDR/orapwoltpdr /tmp/orapwolapdr
ASMCMD> pwcopy /tmp/orapwolapdr +DATA/OLAPDR/orapwolapdr

Check standby (oltpdr) and don’t forget to defer and enable dest.

oltp1> alter system set log_archive_dest_state_5=defer;

System altered.

oltp1> alter system set log_archive_dest_state_5=enable

oltp1> @dataguard.sql

Session altered.

*** v$database ***

DB              OPEN                   DATABASE                                REMOTE     SWITCHOVER         DATAGUARD  PRIMARY_DB
UNIQUE_NAME     MODE                   ROLE               PROTECTION_MODE      ARCHIVE    STATUS             BROKER     UNIQUE_NAME
--------------- ---------------------- ------------------ -------------------- ---------- ------------------ ---------- ---------------
oltpdr          READ ONLY WITH APPLY   PHYSICAL STANDBY   MAXIMUM PERFORMANCE  ENABLED    NOT ALLOWED        ENABLED    oltp

*** gv$archive_dest_status ***
                             DB                                        DATABASE                     RECOVERY
 INST  DEST TARGET           UNIQUE_NAME     DESTINATION               MODE            STATUS       MODE                    SCHEDULE PROCESS
----- ----- ---------------- --------------- ------------------------- --------------- ------------ ----------------------- -------- --------
    1     1 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST OPEN_READ-ONLY  VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
          5 REMOTE           olapdr          olapdr                    MOUNTED-STANDBY VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
         32 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST UNKNOWN         VALID        IDLE                    ACTIVE   RFS

    2     1 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST OPEN_READ-ONLY  VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
          5 REMOTE           olapdr          olapdr                    MOUNTED-STANDBY VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
         32 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST UNKNOWN         VALID        IDLE                    ACTIVE   RFS

6 rows selected.

 INST  DEST STATUS       SRL GAP_STATUS      ERROR
----- ----- ------------ --- --------------- --------------------------------------------------------------------------------
    1     1 VALID        NO                  NONE
          5 VALID        YES RESOLVABLE GAP  NONE
         32 VALID        NO                  NONE

    2     1 VALID        NO                  NONE
          5 VALID        YES RESOLVABLE GAP  NONE
         32 VALID        NO                  NONE

6 rows selected.

Check standby (olapdr)

olap1> @dataguard.sql

Session altered.

*** v$database ***

DB              OPEN                   DATABASE                                REMOTE     SWITCHOVER         DATAGUARD  PRIMARY_DB
UNIQUE_NAME     MODE                   ROLE               PROTECTION_MODE      ARCHIVE    STATUS             BROKER     UNIQUE_NAME
--------------- ---------------------- ------------------ -------------------- ---------- ------------------ ---------- ---------------
olapdr          MOUNTED                PHYSICAL STANDBY   MAXIMUM PERFORMANCE  ENABLED    NOT ALLOWED        DISABLED   oltp

*** gv$archive_dest_status ***
                             DB                                        DATABASE                     RECOVERY
 INST  DEST TARGET           UNIQUE_NAME     DESTINATION               MODE            STATUS       MODE                    SCHEDULE PROCESS
----- ----- ---------------- --------------- ------------------------- --------------- ------------ ----------------------- -------- --------
    1     1 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST MOUNTED-STANDBY VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
         32 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST UNKNOWN         VALID        IDLE                    ACTIVE   RFS

    2     1 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST MOUNTED-STANDBY VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
         32 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST UNKNOWN         VALID        IDLE                    ACTIVE   RFS

 INST  DEST STATUS       SRL GAP_STATUS      ERROR
----- ----- ------------ --- --------------- --------------------------------------------------------------------------------
    1     1 VALID        NO                  NONE
         32 VALID        NO                  NONE

    2     1 VALID        NO                  NONE
         32 VALID        NO                  NONE

*** v$archived_log ***

TIME                  THREAD# ARCHIVED  APPLIED      GAP
-------------------- -------- -------- -------- --------
23-SEP-2018 09:26:05        1    37550    37467       83
23-SEP-2018 09:26:05        2    32631    32566       65

*** gv$managed_standby ***
                                        CLIENT                                               DELAY
 INST PID                       THREAD# PROCESS    PROCESS  STATUS       SEQUENCE#   BLOCK#   MINS
----- ------------------------ -------- ---------- -------- ------------ --------- -------- ------
    1 399156                          2 N/A        MRP0     APPLYING_LOG     32570   721960      0

olap1> r
  1  select inst_id inst,PID,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where status not in ('CLOSING','IDLE','CONNECTED')
  4  order by inst_id, status desc, thread#, sequence#
  5*
                                        CLIENT                                               DELAY
 INST PID                       THREAD# PROCESS    PROCESS  STATUS       SEQUENCE#   BLOCK#   MINS
----- ------------------------ -------- ---------- -------- ------------ --------- -------- ------
    1 399156                          2 N/A        MRP0     APPLYING_LOG     32570   733658      0

olap1> r
  1  select inst_id inst,PID,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where status not in ('CLOSING','IDLE','CONNECTED')
  4  order by inst_id, status desc, thread#, sequence#
  5*

                                       CLIENT                                               DELAY
 INST PID                       THREAD# PROCESS    PROCESS  STATUS       SEQUENCE#   BLOCK#   MINS
----- ------------------------ -------- ---------- -------- ------------ --------- -------- ------
    1 399156                          1 N/A        MRP0     APPLYING_LOG     37474   499677      0
    2 366691                          2 UNKNOWN    RFS      RECEIVING        32632  1073153      0

olap1> 

Next, configure DataGuard Broker and not looking pretty.

RMAN-03002: ORA-19693: backup piece already included

I have been cursed trying to create 25TB standby database.

Active duplication using standby as source failed due to bug.

Backup based duplication using standby as source failed due to bug again.

Now performing traditional restore.

Both attempts failed with RMAN-20261: ambiguous backup piece handle

RMAN> list backuppiece '/bkup/ovtdkik0_1_1.bkp';
RMAN> change backuppiece '/bkup/ovtdkik0_1_1.bkp' uncatalog;

What’s in the backup?

RMAN> spool log to /tmp/list.log
RMAN> list backup;
RMAN> exit

There are 2 identical backuppiece and don’t know how this could have happened.

$ grep ovtdkik0_1_1 /tmp/list.log
    201792  1   AVAILABLE   /bkup/ovtdkik0_1_1.bkp
    202262  1   AVAILABLE   /bkup/ovtdkik0_1_1.bkp

RMAN> delete backuppiece 202262;

Restart restore and is running again.

Don’t Drop Your Career Using Drop Database

I first learned about drop database in 2007.

Environment contains standby database oltpdr.
Duplicate standby database olapdr on the same host using oltpdr as source failed during restore phase.
Clean up data files from failed olapdr duplication.

Check database olapdr.

olap1> show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      oltp
db_unique_name                       string      olapdr

olap1> select count(*) from gv$session;

  COUNT(*)
----------
        90

Elapsed: 00:00:00.00
olap1> select open_mode from v$database;

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

Elapsed: 00:00:00.03
olap1> startup force mount restrict exclusive;
ORACLE instance started.

Total System Global Area 2.5770E+10 bytes
Fixed Size                  6870952 bytes
Variable Size            5625976920 bytes
Database Buffers         1.9998E+10 bytes
Redo Buffers              138514432 bytes
Database mounted.

olap1> show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      oltp
db_unique_name                       string      olapdr

olap1> select count(*) from gv$session;

  COUNT(*)
----------
        92

Elapsed: 00:00:00.01
olap1> select open_mode from v$database;

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

Elapsed: 00:00:00.04

At this point, I was ready to run drop database and somehow an angel was watching over me and I decided to check v$datafile.

olap1> select name from v$datafile where rownum < 10;

NAME
-----------------------------------------------------------
+DATA/OLTPDR/DATAFILE/system.4069.986394171
+DATA/OLTPDR/DATAFILE/dev_odi_temp.4067.986394187
+DATA/OLTPDR/DATAFILE/sysaux.4458.985845085
+DATA/OLTPDR/DATAFILE/big_dmstaging_data_new_2.4687.986498821
+DATA/OLTPDR/DATAFILE/account_toll_index.3799.985714921
+DATA/OLTPDR/DATAFILE/users.2524.985777377
+DATA/OLTPDR/DATAFILE/dev_ias_temp.4141.985846937
+DATA/OLTPDR/DATAFILE/dev_stb.4143.985846937
+DATA/OLTPDR/DATAFILE/dev_odi_user.4144.985846937

9 rows selected.

Elapsed: 00:00:00.01

olap1> exit

Strange data files are the same for source and target.

oltp1> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Elapsed: 00:00:00.07
oltp1> select name from v$datafile where rownum < 10;

NAME
-----------------------------------------------------------
+DATA/OLTPDR/DATAFILE/system.4069.986394171
+DATA/OLTPDR/DATAFILE/dev_odi_temp.4067.986394187
+DATA/OLTPDR/DATAFILE/sysaux.4458.985845085
+DATA/OLTPDR/DATAFILE/big_dmstaging_data_new_2.4687.986498821
+DATA/OLTPDR/DATAFILE/account_toll_index.3799.985714921
+DATA/OLTPDR/DATAFILE/users.2524.985777377
+DATA/OLTPDR/DATAFILE/dev_ias_temp.4141.985846937
+DATA/OLTPDR/DATAFILE/dev_stb.4143.985846937
+DATA/OLTPDR/DATAFILE/dev_odi_user.4144.985846937

9 rows selected.

Elapsed: 00:00:00.01
oltp1> exit

Check data files from ASM.

ASMCMD> cd DATA
ASMCMD> ls
OLAPDR/
OLTP/
OLTPDR/
SCHDDBDR/
_MGMTDB/

ASMCMD> cd OLAPDR
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ASMCMD> cd DATAFILE
ASMCMD> pwd
+DATA/OLAPDR/DATAFILE
ASMCMD> exit

Shutdown olapdr.

olap1> show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      oltp
db_unique_name                       string      olapdr

olap1> select open_mode from v$database;

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

Elapsed: 00:00:00.03
olap1> shut abort;
ORACLE instance shut down.
olap1> exit

Manually remove data files from ASM.

$ asmcmd lsof -G +DATA|grep -ic OLAPDR
0
$ asmcmd ls +DATA/OLAPDR/DATAFILE|wc -l
1665
$ asmcmd lsof -G +DATA/OLAPDR/DATAFILE|wc -l
0
$ asmcmd
ASMCMD> cd datac1
ASMCMD> cd olapdr
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ASMCMD> cd datafile
ASMCMD> pwd
+DATA/olapdr/datafile
ASMCMD> rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y

What would have happened if drop database was executed?
Does anyone know for sure?
Would you have executed drop database?

Differences Between Validate Preview [Summary]

Summary is equivalent to: list backup of database summary.
Without summary is equivalent to: list backup of database.

RMAN> restore database validate preview summary from tag=stby_dup;

Starting restore at 20-SEP-2018 21:19:48
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 instance=hawk1 device type=DISK


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
119     B  0  A DISK        18-SEP-2018 13:56:33 1       1       NO         STBY_DUP
using channel ORA_DISK_1

RMAN> restore database validate preview from tag=stby_dup;

Starting restore at 20-SEP-2018 21:18:44
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 instance=hawk1 device type=DISK


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
119     Incr 0  1.34G      DISK        00:00:15     18-SEP-2018 13:56:33
        BP Key: 121   Status: AVAILABLE  Compressed: NO  Tag: STBY_DUP
        Piece Name: /tmp/HAWK_djtde1c3_1_1.bkp
  List of Datafiles in backup set 119
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 6038608    18-SEP-2018 13:56:19 +DATA/hawkb/datafile/system.306.984318067
  2    0  Incr 6038608    18-SEP-2018 13:56:19 +DATA/hawkb/datafile/sysaux.307.984318067
  3    0  Incr 6038608    18-SEP-2018 13:56:19 +DATA/hawkb/datafile/undotbs1.309.984318093
  4    0  Incr 6038608    18-SEP-2018 13:56:19 +DATA/hawkb/datafile/users.310.984318093
  5    0  Incr 6038608    18-SEP-2018 13:56:19 +DATA/hawkb/datafile/undotbs2.311.984318095
  6    0  Incr 6038608    18-SEP-2018 13:56:19 +DATA/hawkb/datafile/undotbs3.312.984318095
using channel ORA_DISK_1

Following is the same for both.
RMAN> restore database validate preview summary from tag=stby_dup;
RMAN> restore database validate preview from tag=stby_dup;

List of Archived Log Copies for database with db_unique_name HAWKB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
849     1    506     A 18-SEP-2018 13:55:08
        Name: +FRA/hawkb/archivelog/2018_09_18/thread_1_seq_506.551.987170199

852     1    507     A 18-SEP-2018 13:56:39
        Name: +FRA/hawkb/archivelog/2018_09_18/thread_1_seq_507.552.987199227

856     1    508     A 18-SEP-2018 22:00:26
        Name: +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_508.554.987220639

860     1    509     A 19-SEP-2018 03:57:18
        Name: +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_509.556.987258729

862     1    510     A 19-SEP-2018 14:32:07
        Name: +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_510.557.987285627

864     1    511     A 19-SEP-2018 22:00:27
        Name: +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_511.558.987287235

868     1    512     A 19-SEP-2018 22:27:15
        Name: +FRA/hawkb/archivelog/2018_09_20/thread_1_seq_512.560.987325879

872     1    513     A 20-SEP-2018 09:11:18
        Name: +FRA/hawkb/archivelog/2018_09_20/thread_1_seq_513.562.987364831

847     2    173     A 18-SEP-2018 13:55:08
        Name: +FRA/hawkb/archivelog/2018_09_18/thread_2_seq_173.550.987170199

854     2    174     A 18-SEP-2018 13:56:38
        Name: +FRA/hawkb/archivelog/2018_09_19/thread_2_seq_174.553.987210305

858     2    175     A 19-SEP-2018 01:05:05
        Name: +FRA/hawkb/archivelog/2018_09_19/thread_2_seq_175.555.987253211

866     2    176     A 19-SEP-2018 13:00:10
        Name: +FRA/hawkb/archivelog/2018_09_19/thread_2_seq_176.559.987287239

870     2    177     A 19-SEP-2018 22:27:18
        Name: +FRA/hawkb/archivelog/2018_09_20/thread_2_seq_177.561.987328815

Media recovery start SCN is 6038608
Recovery must be done beyond SCN 6038608 to clear datafile fuzziness

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /tmp/HAWK_djtde1c3_1_1.bkp
channel ORA_DISK_1: piece handle=/tmp/HAWK_djtde1c3_1_1.bkp tag=STBY_DUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:08
using channel ORA_DISK_1

channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_18/thread_1_seq_506.551.987170199
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_18/thread_1_seq_507.552.987199227
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_508.554.987220639
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_509.556.987258729
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_510.557.987285627
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_511.558.987287235
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_20/thread_1_seq_512.560.987325879
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_20/thread_1_seq_513.562.987364831
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_18/thread_2_seq_173.550.987170199
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_2_seq_174.553.987210305
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_2_seq_175.555.987253211
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_2_seq_176.559.987287239
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_20/thread_2_seq_177.561.987328815
Finished restore at 20-SEP-2018 21:20:11

RMAN>

tnsping for DataGuard

I am preparing Dataguard for switchover with 1 primary and 3 standbys and should be able to tnsping all the services from log_archive_config=DG_CONFIG=(HAWKA,HAWKB,HAWKC)

Not sure how valuable this may be for you as I wanted to perform all the tasks in one command and know where the error is at.

tnsping HAWKC is failing in the 2nd position.

[oracle@db-fs-1 ~]$ { tnsping HAWKA & tnsping HAWKC & tnsping HAWKB & echo ; } > /tmp/tnsping_`hostname -s`; ls -l /tmp/tnsping_`hostname -s`
[1] 18375
[2] 18376
[3] 18377
-rw-r--r-- 1 oracle oinstall 1208 Sep  6 00:45 /tmp/tnsping_db-fs-1
[1]   Done                    tnsping HAWKA
[2]-  Exit 1                  tnsping HAWKC
[3]+  Done                    tnsping HAWKB
[oracle@db-fs-1 ~]$

tnsping HAWKC is failing in the 3rd position.

[oracle@db-fs-1 ~]$ { tnsping HAWKA & tnsping HAWKB & tnsping HAWKC & echo $? ; } > /tmp/tnsping_`hostname -s`; ls -l /tmp/tnsping_`hostname -s`
[1] 18433
[2] 18434
[3] 18435
-rw-r--r-- 1 oracle oinstall 1210 Sep  6 00:46 /tmp/tnsping_db-fs-1
[1]   Done                    tnsping HAWKA
[2]-  Done                    tnsping HAWKB
[3]+  Exit 1                  tnsping HAWKC
[oracle@db-fs-1 ~]$

tnsping HAWKC is failing in the 3rd position.
There were 3 processes spawned and had to press enter to get final results.

[oracle@db-fs-1 ~]$ { tnsping HAWKA & tnsping HAWKB & tnsping HAWKC & echo $? ; } > /tmp/tnsping_`hostname -s`; ls -l /tmp/tnsping_`hostname -s`
[1] 18469
[2] 18470
[3] 18471
-rw-r--r-- 1 oracle oinstall 837 Sep  6 00:47 /tmp/tnsping_db-fs-1
[1]   Done                    tnsping HAWKA
[3]+  Exit 1                  tnsping HAWKC
[oracle@db-fs-1 ~]$
[2]+  Done                    tnsping HAWKB
[oracle@db-fs-1 ~]$

Remove tnsping HAWKC to demo working results.

[oracle@db-fs-1 ~]$ { tnsping HAWKA & tnsping HAWKB & echo ; } > /tmp/tnsping_`hostname -s`; ls -l /tmp/tnsping_`hostname -s`
[1] 18500
[2] 18501
-rw-r--r-- 1 oracle oinstall 955 Sep  6 00:47 /tmp/tnsping_db-fs-1
[1]-  Done                    tnsping HAWKA
[2]+  Done                    tnsping HAWKB
[oracle@db-fs-1 ~]$

UPDATE:
Another preference suggested is to use for loops.

for s in "HAWKA" "HAWKB" "HAWKC"
do
echo $s
tnsping $s >> /tmp/log
done


How Backup Spfile to Pfile Saved My Arse

Typically when I perform backup review, I always suggest to add the following:

run {
allocate channel c1 device type disk;
SQL "alter database backup controlfile to trace as ''/tmp/ctl_@_trace.sql'' reuse resetlogs";
SQL "create pfile=''/tmp/init@.ora'' from spfile";
release channel c1;
}


Example:

RMAN> run {
allocate channel c1 device type disk;
2> 3> SQL "alter database backup controlfile to trace as ''/tmp/ctl_@_trace.sql'' reuse resetlogs";
4> SQL "create pfile=''/tmp/init@.ora'' from spfile";
5> release channel c1;
6> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=31 instance=hawk1 device type=DISK

sql statement: alter database backup controlfile to trace as ''/tmp/ctl_@_trace.sql'' reuse resetlogs

sql statement: create pfile=''/tmp/init@.ora'' from spfile

released channel: c1

RMAN> exit

[oracle@racnode-dc1-1 tmp]$ ll /tmp/init*
-rw-r--r-- 1 oracle dba 1978 Aug 25 21:11 /tmp/inithawk1.ora
[oracle@racnode-dc1-1 tmp]$ ll /tmp/ctl*
-rw-r--r-- 1 oracle dba 7318 Aug 25 21:11 /tmp/ctl_hawk1_trace.sql
[oracle@racnode-dc1-1 tmp]$ sysresv|tail -1
Oracle Instance alive for sid "hawk1"
[oracle@racnode-dc1-1 tmp]$

When on RAC, do not create pfile to it’s default destination, e.g. SQL “create pfile from spfile”;

ORIGINAL LOCATION:
====================================================================================================
$ asmcmd ls -l +DATA/SOXPA/PARAMETERFILE
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   AUG 17 10:00:00  Y    spfile.321.984394591
PARAMETERFILE  UNPROT  COARSE   AUG 17 10:00:00  N    spfilehawka.ora => +DATA/HAWKA/PARAMETERFILE/spfile.321.984394591

BIG OOPS: NOTHING THERE!
====================================================================================================
$ asmcmd ls -l +DATA/HAWKA/PARAMETERFILE

ERROR: Created wrong spfile.
====================================================================================================
$ asmcmd ls -l +DATA/WH02A/PARAMETERFILE
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   AUG 23 19:00:00  Y    spfile.380.984672023
PARAMETERFILE  UNPROT  COARSE   AUG 23 19:00:00  N    spfilewh02a.ora => +DATA/WH02A/PARAMETERFILE/spfile.380.984672023
PARAMETERFILE  UNPROT  COARSE   AUG 24 14:00:00  N    spfilehawka.ora => +DATA/WH01A/PARAMETERFILE/spfile.321.985008497

Create copy of pfile.
====================================================================================================
$ ll *.good
-rw-r--r--. 1 oracle oinstall 2207 Aug 24 14:57 inithawka4.ora.good

Check controlfile location for pfile.
====================================================================================================
$ cat inithawka4.ora.good 
*.control_files='+DATA/hawka/controlfile/current.272.984393927','+FRA/hawka/controlfile/current.307.984393927'#Restore Controlfile

Check database and create new spfile from pfile.
====================================================================================================
HOST04:(SYS@hawka4):PRIMARY> show parameter spfile;

NAME                           TYPE        VALUE
------------------------------ ----------- ----------------------------------------------------------------------------------------------------
spfile                         string      +DATA/hawka/parameterfile/spfilehawka.ora

HOST04:(SYS@hawka4):PRIMARY> show parameter control_file 

NAME                           TYPE        VALUE
------------------------------ ----------- ----------------------------------------------------------------------------------------------------
control_file_record_keep_time  integer     7
control_files                  string      +DATA/hawka/controlfile/current.272.984393927, +FRA/hawka/controlfile/current.307.984393927

HOST04:(SYS@hawka4):PRIMARY> create spfile='+DATA/HAWKA/PARAMETERFILE/spfilehawka4.ora' from pfile='/u01/app/oracle/db/11.2.0.4/dbs/inithawka4.ora.good';

File created.

HOST04:(SYS@hawka4):PRIMARY> exit

rmalias and mkalias for NEW SPFILE.
====================================================================================================
$ asmcmd ls -l +DATA/HAWKA/PARAMETERFILE
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  Y    spfile.1077.985015077
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  N    spfilehawka4.ora => +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077

$ asmcmd
ASMCMD> cd +DATA/HAWKA/PARAMETERFILE
ASMCMD> ls -lt
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  N    spfilehawka4.ora => +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  Y    spfile.1077.985015077

ASMCMD> rmalias spfilehawka4.ora

ASMCMD> mkalias +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077 spfilehawka.ora

ASMCMD> ls -lt
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  N    spfilehawka.ora => +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  Y    spfile.1077.985015077
ASMCMD> exit

$ asmcmd ls -l +DATA/HAWKA/PARAMETERFILE
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  Y    spfile.1077.985015077
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  N    spfilehawka.ora => +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077
$ 

Verify pfile can be created from spfile.
====================================================================================================
HOST04:(SYS@hawka4):PRIMARY> create pfile='/tmp/init@.ora' from spfile;

File created.

HOST04:(SYS@hawka4):PRIMARY> exit
====================================================================================================
oracle@p2dbccx04:hawka4:/home/oracle
$ ll /tmp/inithawka4.ora
-rw-r--r--. 1 oracle asmadmin 2207 Aug 24 15:24 /tmp/inithawka4.ora

RMAN: Synchronize standby database using production archivelog backupset

If you have not read RMAN: Synchronize standby database using production archivelog, then please do so.

# Primary archivelog is on local vs shared storage.
# Primary RMAN archivelog backupset resides on shared folder with Standby.
# Full backup is performed once per day and include archivelog with format arch_DB02_`date '+%Y%m%d'
# MANAGED REAL TIME APPLY is running.
PRI: /shared/prod/DB02/rman/
SBY: /shared/backup/arch/DB02a/

#!/bin/sh -e
# Michael Dinh: Aug 21, 2018
# RMAN sync standby using production archivelog backupset
#
. ~/working/dinh/dinh.env
. ~/working/dinh/DB02a.env
sysresv|tail -1
set -x
# List production archivelog backupset for current day
ls -l /shared/prod/DB02/rman/arch_DB02_`date '+%Y%m%d'`*
# Copy production archivelog backupset for current day to standby
cp -ufv /shared/prod/DB02/rman/arch_DB02_`date '+%Y%m%d'`* /shared/backup/arch/DB02a
rman msglog /tmp/rman_sync_standby.log > /dev/null << EOF
set echo on;
connect target;
show all;
# Catalog production archivelog backupset from standby
catalog start with '/shared/backup/arch/DB02a' noprompt;
# Restore production archivelog backupset to standby
restore archivelog from time 'trunc(sysdate)-1';
exit
EOF
sleep 15m
# Verify Media Recovery Log from alert log
tail -20 $ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log
exit

$ crontab -l
00 12 * * * /home/oracle/working/dinh/rman_sync_standby.sh > /tmp/rman_sync_standby.sh.out 2>&1

$ ll /tmp/rman*
-rw-r--r--. 1 oracle oinstall 7225 Aug 22 12:01 /tmp/rman_sync_standby.log
-rw-r--r--. 1 oracle oinstall 4318 Aug 22 12:16 /tmp/rman_sync_standby.sh.out

+ tail -20 /u01/app/oracle/diag/rdbms/DB02a/DB02a2/trace/alert_DB02a2.log
ALTER DATABASE RECOVER  managed standby database using current logfile nodelay disconnect  
ORA-1153 signalled during: ALTER DATABASE RECOVER  managed standby database using current logfile nodelay disconnect  ...
Tue Aug 21 15:41:27 2018
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Tue Aug 21 15:54:30 2018
db_recovery_file_dest_size of 204800 MB is 21.54% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Aug 22 12:01:21 2018
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31636.1275.984830461
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31637.1276.984830461
Wed Aug 22 12:01:46 2018
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31638.1278.984830487
Wed Aug 22 12:01:58 2018
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31639.1277.984830487
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31640.1279.984830487
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31641.1280.984830487
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31642.1281.984830489
Media Recovery Waiting for thread 1 sequence 31643
+ exit

# Manual recovery: WAIT_FOR_LOG and BLOCK#=0 and never increment.
SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where 1=1
  4  and status not in ('CLOSING','IDLE','CONNECTED')
  5  order by status desc, thread#, sequence#
  6*

                        CLIENT                                               DELAY
     PID  INST  THREAD# PROCESS    PROCESS  STATUS       SEQUENCE#   BLOCK#   MINS
-------- ----- -------- ---------- -------- ------------ --------- -------- ------
   94734     2        1 N/A        MRP0     WAIT_FOR_LOG     31643        0      0

SQL>

$ cat /tmp/rman_sync_standby.log 

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 22 12:00:58 2018

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

RMAN> 
echo set on

RMAN> connect target;
connected to target database: DB02 (DBID=1816794213, not open)

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DB02A are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 7;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libddobk.so, ENV=(STORAGE_UNIT=dd-u99,BACKUP_HOST=dd860.ccx.carecentrix.com,ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/db/11.2.0.4/dbs/snapcf_DB02a2.f'; # default

RMAN> catalog start with '/shared/backup/arch/DB02a' noprompt;
searching for all files that match the pattern /shared/backup/arch/DB02a

List of Files Unknown to the Database
=====================================
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1
File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1

RMAN> restore archivelog from time 'trunc(sysdate)-1';
Starting restore at 22-AUG-2018 12:01:00
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=285 instance=DB02a2 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=3 instance=DB02a2 device type=DISK

archived log for thread 1 with sequence 31630 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31630.496.984755257
archived log for thread 1 with sequence 31631 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31631.497.984755273
archived log for thread 1 with sequence 31632 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31632.498.984755273
archived log for thread 1 with sequence 31633 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31633.499.984755275
archived log for thread 1 with sequence 31634 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31634.500.984755275
archived log for thread 1 with sequence 31635 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31635.501.984755275
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=31636
channel ORA_DISK_1: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=31637
channel ORA_DISK_2: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1
channel ORA_DISK_1: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1 tag=TAG20180822T110121
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=31638
channel ORA_DISK_1: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1
channel ORA_DISK_2: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1 tag=TAG20180822T110121
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:25
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=31639
channel ORA_DISK_2: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1
channel ORA_DISK_2: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1 tag=TAG20180822T113906
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=31640
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=31641
channel ORA_DISK_2: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1
channel ORA_DISK_2: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1 tag=TAG20180822T113906
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=31642
channel ORA_DISK_2: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1
channel ORA_DISK_2: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1 tag=TAG20180822T113906
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1 tag=TAG20180822T110121
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:17
Finished restore at 22-AUG-2018 12:01:44

RMAN> exit

$ cat /tmp/rman_sync_standby.sh.out 
ORACLE_SID = [oracle] ? The Oracle base has been set to /u01/app/oracle
Oracle Instance alive for sid "DB02a2"
CURRENT_INSTANCE=DB02a2
ORACLE_UNQNAME=DB02a
OTHER_INSTANCE=DB02a3,DB02a4
ORACLE_SID=DB02a2
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/db/11.2.0.4
NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
Oracle Instance alive for sid "DB02a2"
++ date +%Y%m%d
+ ls -l /shared/prod/DB02/rman/arch_DB02_20180822_9ttb6h01_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_9utb6h02_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_9vtb6h02_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_a9tb6j6q_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_aatb6j6q_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_abtb6j6q_1_1
-rw-r-----. 1 oracle dba 1900124160 Aug 22 11:02 /shared/prod/DB02/rman/arch_DB02_20180822_9ttb6h01_1_1
-rw-r-----. 1 oracle dba 1938098176 Aug 22 11:02 /shared/prod/DB02/rman/arch_DB02_20180822_9utb6h02_1_1
-rw-r-----. 1 oracle dba 1370842112 Aug 22 11:01 /shared/prod/DB02/rman/arch_DB02_20180822_9vtb6h02_1_1
-rw-r-----. 1 oracle dba   11870720 Aug 22 11:39 /shared/prod/DB02/rman/arch_DB02_20180822_a9tb6j6q_1_1
-rw-r-----. 1 oracle dba       3584 Aug 22 11:39 /shared/prod/DB02/rman/arch_DB02_20180822_aatb6j6q_1_1
-rw-r-----. 1 oracle dba       3072 Aug 22 11:39 /shared/prod/DB02/rman/arch_DB02_20180822_abtb6j6q_1_1
++ date +%Y%m%d
+ cp -ufv /shared/prod/DB02/rman/arch_DB02_20180822_9ttb6h01_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_9utb6h02_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_9vtb6h02_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_a9tb6j6q_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_aatb6j6q_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_abtb6j6q_1_1 /shared/backup/arch/DB02a
\u2018/shared/prod/DB02/rman/arch_DB02_20180822_9ttb6h01_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1\u2019
\u2018/shared/prod/DB02/rman/arch_DB02_20180822_9utb6h02_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1\u2019
\u2018/shared/prod/DB02/rman/arch_DB02_20180822_9vtb6h02_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1\u2019
\u2018/shared/prod/DB02/rman/arch_DB02_20180822_a9tb6j6q_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1\u2019
\u2018/shared/prod/DB02/rman/arch_DB02_20180822_aatb6j6q_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1\u2019
\u2018/shared/prod/DB02/rman/arch_DB02_20180822_abtb6j6q_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1\u2019
+ rman msglog /tmp/rman_sync_standby.log
+ sleep 15m
+ tail -20 /u01/app/oracle/diag/rdbms/DB02a/DB02a2/trace/alert_DB02a2.log
ALTER DATABASE RECOVER  managed standby database using current logfile nodelay disconnect  
ORA-1153 signalled during: ALTER DATABASE RECOVER  managed standby database using current logfile nodelay disconnect  ...
Tue Aug 21 15:41:27 2018
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Tue Aug 21 15:54:30 2018
db_recovery_file_dest_size of 204800 MB is 21.54% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Aug 22 12:01:21 2018
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31636.1275.984830461
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31637.1276.984830461
Wed Aug 22 12:01:46 2018
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31638.1278.984830487
Wed Aug 22 12:01:58 2018
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31639.1277.984830487
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31640.1279.984830487
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31641.1280.984830487
Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31642.1281.984830489
Media Recovery Waiting for thread 1 sequence 31643
+ exit