Auto shutdown database and copy datafiles to desired location

sqlplus sys/oracle_4me@db11gdev as sysdba @G:\alert\shut.sql


1. This batch file will call the 2nd script (shut.sql).

Below script withh shutdown the database and copy the all datafiles to desired destination.

shutdown immediate

host copy G:\DB11GDEV\DB11GDEV\*.dbf   H:\target

How to Check which tables are STALE

select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where owner='&owner' and STALE_STATS='YES';

SOLUTION: If you fount stale tables gather the stats as below.

=== gather stat TABLE====

exec dbms_stats.gather_table_stats (ownname=>'ASHWANI',tabname=>'EMP',estimate_percent=>100);

----gather stat TABLE WITH INDEXES

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ASHWANI' , tabname => 'EMP',cascade => true, estimate_percent => 10,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);

--Schema. The Gather Schema Statistics process should be run on a regular basis (weekly at a minimum) and anytime large changes to the data or database take place. 

Example:

exec dbms_stats.gather_schema_stats(ownname => 'ASHWANI', estimate_percent => 25)

How to move older trace files to another location in Window Server.

Step 1) Make a directory called "moved"

D:\>mkdir D:\app\oracle\admin\orcl\Adump\moved

Step 2) Create a batch file mv_trc.bat and copy the below content to this batch file.


D:


cd \app\oracle\admin\orcl\Adump


forfiles /S /D -365 /C "cmd /c move @file D:\app\oracle\admin\orcl\Adump\moved"

----Above forfiles command will move the all the trace files created 365 days before to the said location---


Step 3) Scheduled the task from window scheduler with below details.

Create a Basic Task and should be run as high privelege.

PARAMETERS:

ACTION: C:\Users\ashwani\Desktop\mv_trc.bat

How To Find Users Who Tried To Login To Database.

############################################################################
Below Script Used To Fetched The Report Of Users Who Tried But Failed To Login To Database in CSV Format 
############################################################################

-- -----------------------------------------------------------------------------------
-- Author         : Ashwani Kumar
-- Description  : Failed Login Notification.
-- Requirements : Access to the v$views.
-- Last Modified: 09/Oct/2017
-- -----------------------------------------------------------------------------------

set markup html on
set echo on
set feedback off
set serverout off
set feedback off

define file_time=date
column tm new_value file_time
select 'Notification_DBNAME_'||SYSDATE||'.xls' tm from dual;

spool C:\&file_time

Select name, open_mode, database_role from v$database;

set echo off
prompt ***********************FAILED LOGIN ATTEMPT*************************************

variable KEY1 NUMBER;
exec :KEY1 := '&NUMBER_OF_DAYS';
col action_name format a10
SELECT
  TO_CHAR(TIMESTAMP,'DD-MON-YYYY hh24:mi') LOGIN_DATE,
  SUBSTR(OS_USERNAME,1,20) OS_USERNAME,
  SUBSTR(USERNAME,1,20) USERNAME,
  SUBSTR(TERMINAL,1,20) TERMINAL,
  ACTION_NAME,
  DECODE(RETURNCODE,0,'SUCCESS',1017,'FAILED LOGIN',28000,'ACCOUNTLOCKED AFTER FAILED LOGIN') AS Message
FROM
  SYS.DBA_AUDIT_SESSION
WHERE
  TIMESTAMP BETWEEN SYSDATE-&days AND SYSDATE and returncode <> 0
ORDER BY TIMESTAMP ASC ;

spool off

set markup html off

All RMPS for Oracle Linux

http://yum.oracle.com/repo/OracleLinux/OL7/UEKR3/x86_64/index.html

How to Configure and Send Database Heath Report Via Mail

[oracle@Prod]$ crontab -l

51 19 * * * /home/oracle/ORACLE_HEALTH_REPORT/Health_CHECK.sh > /home/oracle/ORACLE_HEALTH_REPORT/Check_Health_`date +\%d-\%b-\%Y_\%H_\%M_\%S`.log

[oracle@Prod]$ vi /home/oracle/ORACLE_HEALTH_REPORT/Health_CHECK.sh

ORACLE_HOSTNAME=hostname; export ORACLE_HOSTNAME

ORACLE_UNQNAME=oracle; export ORACLE_UNQNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=oracle; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

#!/bin/ksh

sqlplus /nolog << EOF

CONNECT / as sysdba

@/home/oracle/ORACLE_HEALTH_REPORT/DB_Health_Report.sql

EXIT;

EOF

echo "TEST" | mail -s "Database Health Report" -a /home/oracle/ORACLE_HEALTH_REPORT/`ls -lrt /home/oracle/ORACLE_HEALTH_REPORT/ | grep "HEALTH_REPORT_ORACLE" | tail -1 | awk '{print $9}'` oracleforensic4u@gmail.com

[oracle@ashwanik ~]$ cat /home/oracle/ORACLE_HEALTH_REPORT/DB_Health_Report.sql

set pages 200 lines 172

col dat1 NEW_VALUE v_start_time

select to_char(sysdate,'YYYY-MM-DD_HH24MISS') dat1 from dual;

select name||'_'||to_char(sysdate,'DD-MM-YYYY_HH24_MI_SS') dat1 from v$database;

spool /home/oracle/ORACLE_HEALTH_REPORT/HEALTH_REPORT_&v_start_time..txt

prompt ###################  Database Status #############################################

select dbid,name,open_mode,database_role,protection_mode from v$database;

select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE from v$instance;

select status,count(1) from v$session group by status order by 2 desc;

prompt ################### Database Size ################################################

select sum(bytes)/1024/1024||' MB' as Total_Size from dba_data_files;

select sum(bytes)/1024/1024||' MB' as USED_Size from dba_segments;

prompt ################### Count of Invalid Objects ###########################################

select owner,object_type,count(1) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;

prompt ####################### Tablespace Usage Report ##############################################

col "NAME" format a30

 set lines 140

 set pages 1000

 select a.tablespace_name "NAME",

        round((b.totalspace - a.freespace),1)"USED_SPACE_MB",

        round(a.freespace,1) "FREE_SPACE_MB",

        round(b.totalspace) "TOTAL_SPACE_MB",

        round(100 * (a.freespace / b.totalspace)) "%_FREE"

 from

  ( select tablespace_name,

        sum(bytes)/1024/1024 TotalSpace

   from dba_data_files

   group by tablespace_name) b,

 ( select tablespace_name,

    sum(bytes)/1024/1024 FreeSpace

   from dba_free_space

   group by tablespace_name) a

  where b.tablespace_name = a.tablespace_name(+)

  order by 5;

prompt ################################ TEMPSPACE USAGE ###################################################

SELECT   A.tablespace_name tablespace, D.mb_total,

         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

                  D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

                  FROM     v$sort_segment A,

                           (

                                    SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024

                                    mb_total

                                             FROM     v$tablespace B, v$tempfile C

                                                      WHERE    B.ts#= C.ts#

                                                               GROUP BY B.name, C.block_size

                                                                        ) D

                                                                        WHERE    A.tablespace_name = D.name

                                                                        GROUP by A.tablespace_name, D.mb_total;

prompt ################################# UNDO USAGE ########################################################

select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB

from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name

from dba_data_files a, dba_tablespaces b

where a.tablespace_name = b.tablespace_name

and b.contents = 'UNDO'

group by b.tablespace_name) a,

(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB

from DBA_UNDO_EXTENTS c

where status <> 'EXPIRED'

group by c.tablespace_name) b

where a.tablespace_name = b.tablespace_name;

select status,

  round(sum_bytes / (1024*1024), 0) as MB,

  round((sum_bytes / undo_size) * 100, 0) as PERC

from

(

  select status, sum(bytes) sum_bytes

  from dba_undo_extents

  group by status

),

(

  select sum(a.bytes) undo_size

  from dba_tablespaces c

    join v$tablespace b on b.name = c.tablespace_name

    join v$datafile a on a.ts# = b.ts#

  where c.contents = 'UNDO'

    and c.status = 'ONLINE'

);

prompt ################################## RMAN BACKUP JOB COMPLETION ##################################################

set pages 500

col INPUT_TYPE Format a15

col status format a15

col OPTIMIZED format a15

col start_time format a20

col end_time format a20

col SESSION_KEY Format 99999

col elapsed_seconds format 999

select

        INPUT_TYPE,

        SESSION_KEY,

        To_char(START_TIME,'DD-MON-YYYY HH24:MI') START_TIME,

        INPUT_BYTES/1024/1024 INPUT,

        STATUS,

        output_bytes/1024/1024 OUTPUT,

        To_Char(END_TIME,'DD-MON-YYYY HH24:MI') END_TIME,

        OPTIMIZED,

        ELAPSED_SECONDS/60 ELAP_MIN

FROM V$RMAN_BACKUP_JOB_DETAILS order by 2;

prompt ################################### INVALID OBJECTS ###########################################################

select blocking_session,sql_id , sid, serial#, wait_class, seconds_in_wait from v$session where blocking_session is not NULL order by  sql_id, blocking_session;

prompt

prompt ############################ INVALID INDEXES ###########################################################

select owner,index_name,table_name,status,degree from dba_indexes where status='INVALID';

select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD ONLINE PARALLEL 4;' from dba_indexes where status='INVALID';

prompt ####################################### Datafile Status #################################################################

col file_name format a50

set pages 500 lines 165

select file_name,bytes/1024/1024,tablespace_name,status,online_status,autoextensible,maxbytes/1024/1024 from dba_data_files  order by 3;

spool off

You have mail in /var/spool/mail/oracle

------------------- REFRESH PDBs (Oracle Ver 12cR2) ------------------

Shema Refresh (ashwani)

1.       Check Listener

[oracle@0PROD ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 24-MAY-2017 17:31:34

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0PROD)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production

Start Date                20-APR-2017 16:39:37

Uptime                    34 days 0 hr. 51 min. 57 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/0PROD/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0PROD)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "501c3dcd870d7f1ae0534c3a1dac7310" has 1 instance(s).

  Instance "smrp", status READY, has 1 handler(s) for this service...

Service "db12c" has 2 instance(s).

  Instance "db12c", status UNKNOWN, has 1 handler(s) for this service...

  Instance "db12c", status READY, has 1 handler(s) for this service...

Service "db12cXDB" has 1 instance(s).

  Instance "db12c", status READY, has 1 handler(s) for this service...

Service "db12cdr" has 1 instance(s).

  Instance "db12cdr", status UNKNOWN, has 1 handler(s) for this service...

Service "orcl" has 2 instance(s).

  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...

  Instance "orcl", status READY, has 1 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

Service "pdb1" has 1 instance(s).

  Instance "smrp", status READY, has 1 handler(s) for this service...

Service "smrp" has 1 instance(s).

  Instance "smrp", status READY, has 1 handler(s) for this service...

Service "smrpXDB" has 1 instance(s).

  Instance "smrp", status READY, has 1 handler(s) for this service...

The command completed successfully

2.    export ORACLE_SID=CDB_name

[oracle@0PROD ~]$ export ORACLE_SID=smrp

[oracle@0PROD ~]$ echo $ORACLE_SID

Smrp

 

[oracle@0PROD ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.2.0.1.0 Production on Wed May 24 17:42:47 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> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

SQL>

3.    Adding tnsname.ora pdb1 (as highlighted) connection

[oracle@0PROD admin]$ cd $ORACLE_HOME/network/admin/

[oracle@0PROD admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

LISTENER_DB12C =

  (ADDRESS = (PROTOCOL = TCP)(HOST = PROD)(PORT = 1521))

LISTENER_ORCL =

  (ADDRESS = (PROTOCOL = TCP)(HOST = PROD)(PORT = 1521))

LISTENER =

  (ADDRESS = (PROTOCOL = TCP)(HOST = PROD)(PORT = 1521))

 

 

DB12CDR =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = TEST)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = db12cdr)

    )

  )

 

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = PROD)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

ORCLDR =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = TEST)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcldr)

    )

  )

 

DB12C =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = PROD)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = db12c)

    )

  )

PDB1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = PROD)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = pdb1)

    )

  )

 

4.    Create directory for PDB

[oracle@0PROD ~]$ mkdir p6_dir

[oracle@0PROD ~]$ cd p6_dir/

[oracle@0PROD p6_dir]$ pwd

/home/oracle/p6_dir

[oracle@0PROD p6_dir]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.2.0.1.0 Production on Wed May 24 17:55:19 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> show pdbs;

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

 

 

SQL> alter session set container=pdb1;

 

Session altered.

SQL> create or replace directory p6_dir as '/home/oracle/p6_dir';

Directory created.

SQL>exit

5.    SCP the dumpfile to test database server

[oracle@0PROD p6_dir]$ pwd

/home/oracle/p6_dir

[oracle@0PROD p6_dir]$ ls –lrth

-rw-r-----. 1 oracle oinstall 340K May 24 17:26 ashwani.dmp

-rw-r--r--. 1 oracle oinstall 1.4K May 24 17:26 ashwani.log

[oracle@0PROD p6_dir]$ scp ashwani.dmp TEST:/home/oracle/p6_dir

ashwani.dmp                                             100%  340KB 340.0KB/s   00:00

[oracle@TEST p6_dir]$ impdp system/oracle_4U@pdb1 directory=pdb1_dir dumpfile=ashwani.dmp logfile=ashwani_imp1.log

 

Import: Release 12.2.0.1.0 - Production on Wed May 24 18:18:45 2017

 

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

 

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

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@pdb1 directory=pdb1_dir dumpfile=ashwani.dmp logfile=ashwani_imp1.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "ASHWANI"."TEST"                            5.531 KB       4 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed May 24 18:19:04 2017 elapsed 0 00:00:19

ARCHIVELOG DELETION POLICY FOR STANDBY DATABASE IN ORACLE DATAGUARD (Oracle Ver 12cR2)

This policy is make sure that the backup of archivelog done before an archivelog is deleted on standby database.

PRACTICAL STEPS ARE AS BELOW:-

ON PROD:

#######################################################################################

FIRST CONFIGURE ARCHIVELOG DELETION POLICY TO "APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK"

#######################################################################################

[oracle@PROD ~]$ rman target sys/oracle_4U@orcl

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Aug 10 17:03:29 2017

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

connected to target database: ORCL (DBID=1478070817)

BEFORE:

RMAN> SHOW ALL;

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

old RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

new RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

new RMAN configuration parameters are successfully stored

AFTER:

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

RMAN>

ON STANDBY:

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE

---------    --------------------

ORCL         MOUNTED

########################################################################

#--------- CHECK THE LAST ARCHIVE RECEIVED FROM PRODUCTION   --------------------------------------------#

########################################################################

SQL> select thread#, max(sequence#) "Last Standby Seq Received"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

group by thread# order by 1;

   THREAD# Last Standby Seq Received

---------- -------------------------

1                                       194

                                     

#######################################################################
#-----------------CHECK THE LAST ARCHIVE APPLIED ON STANDBY----------------------------#

#######################################################################

SQL> select max(SEQUENCE#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)

  ---------------------

           186

#####################################################################

#-------------------CHECK THE ARCHIVELOGS WHICH ARE NOT APPLIED ON STANDBY-----------------#

#####################################################################

SQL> select SEQUENCE#,NAME from v$archived_log where applied='NO';

 SEQUENCE# NAME

---------- -----------------------------------------------------------------------------------

       160 orcl

       161 orcl

       187 /u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_187_drrgw23w_.arc

       188 /u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_188_drrgw39r_.arc

       189 /u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_189_drrgw6xy_.arc

       190 /u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_190_drrgw71r_.arc

       191 /u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_191_drrgy3c6_.arc

       192 /u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_192_drrgyz94_.arc

       193 /u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_193_drrh1hll_.arc

       194 /u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_194_drrh2ofs_.arc

10 rows selected.

#######################################################################
LETS TAKE THE FULL BACKUP ALONG WITH ALL ARCHIVELOG WITH DELETE INPUT ON STANDBY 

#######################################################################

[oracle@STDBY]$ rman target sys/oracle_4U@orcldr

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Aug 10 16:56:50 2017

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

connected to target database: ORCL (DBID=1478070817, not open)

RMAN> backup database plus archivelog delete input;

Starting backup at 10-AUG-17

using target database control file instead of recovery catalog

current log archived at primary database

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=125 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=187 RECID=191 STAMP=951668698

input archived log thread=1 sequence=188 RECID=192 STAMP=951668699

input archived log thread=1 sequence=189 RECID=193 STAMP=951668702

input archived log thread=1 sequence=190 RECID=194 STAMP=951668703

input archived log thread=1 sequence=191 RECID=195 STAMP=951668763

input archived log thread=1 sequence=192 RECID=196 STAMP=951668791

input archived log thread=1 sequence=193 RECID=197 STAMP=951668871

input archived log thread=1 sequence=194 RECID=198 STAMP=951668909

input archived log thread=1 sequence=195 RECID=199 STAMP=951670645

channel ORA_DISK_1: starting piece 1 at 10-AUG-17

channel ORA_DISK_1: finished piece 1 at 10-AUG-17

piece handle=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/backupset/2017_08_10/o1_mf_annnn_TAG20170810T165726_drrjryg9_.bkp tag=TAG20170810T165726 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process

archived log file name=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_187_drrgw23w_.arc thread=1 sequence=187

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process

archived log file name=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_188_drrgw39r_.arc thread=1 sequence=188

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process

archived log file name=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_189_drrgw6xy_.arc thread=1 sequence=189

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process

archived log file name=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_190_drrgw71r_.arc thread=1 sequence=190

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process

archived log file name=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_191_drrgy3c6_.arc thread=1 sequence=191

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process

archived log file name=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_192_drrgyz94_.arc thread=1 sequence=192

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process

archived log file name=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_193_drrh1hll_.arc thread=1 sequence=193

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process

archived log file name=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_194_drrh2ofs_.arc thread=1 sequence=194

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process

archived log file name=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_195_drrjrwy2_.arc thread=1 sequence=195

Finished backup at 10-AUG-17

Starting backup at 10-AUG-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/ORCLDR/datafile/o1_mf_system_dqgdh8ls_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ORCLDR/datafile/o1_mf_sysaux_dqgdjdg8_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORCLDR/datafile/o1_mf_undotbs1_dqgdk5xc_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/ORCLDR/datafile/o1_mf_users_dqgdk6z4_.dbf

channel ORA_DISK_1: starting piece 1 at 10-AUG-17

channel ORA_DISK_1: finished piece 1 at 10-AUG-17

piece handle=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/backupset/2017_08_10/o1_mf_nnndf_TAG20170810T165727_drrjrzpf_.bkp tag=TAG20170810T165727 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

Finished backup at 10-AUG-17

Starting backup at 10-AUG-17

current log archived at primary database

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=196 RECID=200 STAMP=951670682

channel ORA_DISK_1: starting piece 1 at 10-AUG-17

channel ORA_DISK_1: finished piece 1 at 10-AUG-17

piece handle=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/backupset/2017_08_10/o1_mf_annnn_TAG20170810T165803_drrjt40o_.bkp tag=TAG20170810T165803 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process

archived log file name=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/archivelog/2017_08_10/o1_mf_1_196_drrjt2tm_.arc thread=1 sequence=196

Finished backup at 10-AUG-17

Starting Control File and SPFILE Autobackup at 10-AUG-17

piece handle=/u01/app/oracle/fast_recovery_area/orcldr/ORCLDR/autobackup/2017_08_10/o1_mf_s_951668774_drrjt56h_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 10-AUG-17

RMAN>

WE CAN SEE WARNING "RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process".  THIS INDICATES THAT ARCHIVES ARE RECEIVED BUT NOT APPLIED TO STANDBY DATABASE FROM sequence# 187 TO 195, SO RMAN JUST BACKUP THE ARCHIVES BUT NOT DELETING.