Skip navigation.

Michael Dinh

Syndicate content Thinking Out Loud
Michael T. Dinh, Oracle DBA
Updated: 18 hours 1 min ago

Not Another Standby Monitoring Script

Fri, 2016-01-29 21:55

I know what you are thinking. Not another standby monitoring script, right?

There’s plenty of them out there already and what makes this one any different?

You are probably correct and there’s probably nothing different about this.

Having to work with environments that are inconsistent such as manual standby, dataguard without broker, dataguard with broker,
I have been obsessed to create standby SQL which can accommodate any environment and can be run from primary or standby.

From PRIMARY: Recovery is IDLE
oracle@arrow:hawklas:/media/sf_working/dataguard
$ . oraenv <<< hawklas
ORACLE_SID = [hawklas] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawklas:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:04:05 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawklas        READ WRITE           PRIMARY          ENABLED  TO STANDBY           ENABLED   hawksan

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        PRIMARY ACTIVE   ARCH              0
       1        2 hawksan                   VALID        STANDBY ACTIVE   LGWR              0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN            IDLE                                    NONE
       2 VALID        OPEN_READ-ONLY  IDLE                    NO GAP          NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       2        1 NO             886 29-JAN-2016 17:50:04         4      3.7   --- STANDBY LAG
       2        1 YES            882 29-JAN-2016 17:46:22

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

no rows selected

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    2659        1        1 LNS       LNS      WRITING            887    10153          0

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
From STANDBY: Recovery is IDLE
oracle@arrow:hawklas:/media/sf_working/dataguard
$ . oraenv <<< hawksan
ORACLE_SID = [hawklas] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawksan:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:04:11 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawksan        READ ONLY            PHYSICAL STANDBY ENABLED  NOT ALLOWED          ENABLED   hawklas

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   ARCH              0
       1       32 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   RFS               0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN_READ-ONLY  IDLE                                    NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       1        1 NO             886 29-JAN-2016 17:50:04         4      3.7   --- STANDBY LAG
       1        1 YES            882 29-JAN-2016 17:46:22

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 882 thread 1;

*** v$dataguard_stats ***

NAME                      VALUE              UNIT
------------------------- ------------------ ------------------------------
transport lag             +00 00:00:00       day(2) to second(0) interval
apply lag                 +00 01:14:48       day(2) to second(0) interval

*** gv$managed_standby ***

no rows selected

ARROW:(SYS@hawksan):PHYSICAL STANDBY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
From DG Broker: SET STATE=’APPLY-ON’

Note: Apply Lag is NOT really 1 hour.

Look at the time difference between SQL*Plus sessions.

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:04:11 2016
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:07:28 2016
oracle@arrow:hawksan:/media/sf_working/dataguard
$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       1 hour(s) 17 minutes 16 seconds (computed 1 second ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> EDIT DATABASE hawksan SET STATE='APPLY-ON';
Succeeded.
DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 2 seconds ago)
  Apply Lag:       1 hour(s) 17 minutes 47 seconds (computed 2 seconds ago)
  Apply Rate:      465.00 KByte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> exit
From PRIMARY: Recovery is STILL IDLE
oracle@arrow:hawksan:/media/sf_working/dataguard
$ . oraenv <<< hawklas
ORACLE_SID = [hawksan] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawklas:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:07:20 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawklas        READ WRITE           PRIMARY          ENABLED  TO STANDBY           ENABLED   hawksan

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        PRIMARY ACTIVE   ARCH              0
       1        2 hawksan                   VALID        STANDBY ACTIVE   LGWR              0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN            IDLE                                    NONE
       2 VALID        OPEN_READ-ONLY  IDLE                    NO GAP          NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       2        1 NO             886 29-JAN-2016 17:50:04         4      3.7   --- STANDBY LAG
       2        1 YES            882 29-JAN-2016 17:46:22

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

no rows selected

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    2659        1        1 LNS       LNS      WRITING            887    10402          0

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
From STANDBY: Recovery is MANAGED REAL TIME APPLY
oracle@arrow:hawklas:/media/sf_working/dataguard
$ . oraenv <<< hawksan
ORACLE_SID = [hawklas] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawksan:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:07:28 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawksan        READ ONLY WITH APPLY PHYSICAL STANDBY ENABLED  NOT ALLOWED          ENABLED   hawklas

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   ARCH              0
       1       32 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   RFS               0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN_READ-ONLY  MANAGED REAL TIME APPLY                 NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       1        1 IN-MEMORY      886 29-JAN-2016 17:50:04         1 .0333333   --- STANDBY LAG
       1        1 YES            885 29-JAN-2016 17:50:02

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

NAME                      VALUE              UNIT
------------------------- ------------------ ------------------------------
transport lag             +00 00:00:00       day(2) to second(0) interval
apply lag                 +00 00:00:00       day(2) to second(0) interval

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    4463        1        1 MRP0      N/A      APPLYING_LOG       887    10409          0

ARROW:(SYS@hawksan):PHYSICAL STANDBY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawksan:/media/sf_working/dataguard
$
Let’s check again. From PRIMARY: Recovery is MANAGED REAL TIME APPLY
oracle@arrow:hawksan:/media/sf_working/dataguard
$ . oraenv <<< hawklas
ORACLE_SID = [hawksan] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawklas:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:09:14 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawklas        READ WRITE           PRIMARY          ENABLED  TO STANDBY           ENABLED   hawksan

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        PRIMARY ACTIVE   ARCH              0
       1        2 hawksan                   VALID        STANDBY ACTIVE   LGWR              0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN            IDLE                                    NONE
       2 VALID        OPEN_READ-ONLY  MANAGED REAL TIME APPLY NO GAP          NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       2        1 NO             886 29-JAN-2016 17:50:04         1 .0333333   --- STANDBY LAG
       2        1 YES            885 29-JAN-2016 17:50:02

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

no rows selected

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    2659        1        1 LNS       LNS      WRITING            887    10512          0

ARROW:(SYS@hawklas):PRIMARY>

SQL can be download from my Public Google Drive.

Look all the way to the right under Menu or search page for PublicGoogleDrive


Bad to crosscheck archivelog all

Thu, 2016-01-28 16:44

Typically, it’s not a good idea to have “crosscheck archivelog all;” in backup scripts and
even worse to have “delete expired archivelog all;” since any evidence will be eradicated.

When you don’t crosscheck archivelog and the archivelog is missing, backup archivelog will fail.
When you crosscheck archivelog and archivelog is missing backup archivelog does not fail since RMAN marks archivelog as expired and ignore.

oracle@arrow:hawklas:/tmp
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 28 14:17:35 2016

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

connected to target database: HAWK (DBID=3130551611)
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
5191    1    868     X 2016-JAN-28 11:46:46
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_868_cbnw3vtd_.arc

5201    1    873     A 2016-JAN-28 14:15:44
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc

RMAN> list backup of archivelog sequence 873;
specification does not match any backup in the repository

RMAN> exit
$ mv -v /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc /tmp/
oracle@arrow:hawklas:/tmp
`/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc' -> `/tmp/o1_mf_1_873_cbo4v850_.arc'
oracle@arrow:hawklas:/tmp
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 28 14:18:30 2016

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

connected to target database: HAWK (DBID=3130551611)
RMAN> backup archivelog all;
Starting backup at 2016-JAN-28 14:18:40
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 01/28/2016 14:18:46
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
validation succeeded for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_874_cbo4y4or_.arc RECID=5203 STAMP=902326725
Crosschecked 1 objects

validation failed for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_868_cbnw3vtd_.arc RECID=5191 STAMP=902317692
validation failed for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc RECID=5201 STAMP=902326632
Crosschecked 2 objects

RMAN> backup archivelog all;

sequence=873 is missing and no errors raised.

Starting backup at 2016-JAN-28 14:19:09
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=874 RECID=5203 STAMP=902326725
channel ORA_DISK_1: starting piece 1 at 2016-JAN-28 14:19:11
channel ORA_DISK_2: starting compressed archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=875 RECID=5204 STAMP=902326750
channel ORA_DISK_2: starting piece 1 at 2016-JAN-28 14:19:11
channel ORA_DISK_1: finished piece 1 at 2016-JAN-28 14:19:12
piece handle=/oradata/backup/HAWK_3130551611_20160128_jdqsgqev_1_1_1645_1 tag=TAG20160128T141910 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: finished piece 1 at 2016-JAN-28 14:19:14
piece handle=/oradata/backup/HAWK_3130551611_20160128_jeqsgqev_1_1_1646_1 tag=TAG20160128T141910 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03
Finished backup at 2016-JAN-28 14:19:14

Starting Control File and SPFILE Autobackup at 2016-JAN-28 14:19:14
piece handle=/oradata/backup/HAWK_c-3130551611-20160128-09 comment=NONE
Finished Control File and SPFILE Autobackup at 2016-JAN-28 14:19:21
RMAN> list backup of archivelog sequence 873;
specification does not match any backup in the repository

RMAN> exit


Recovery Manager complete.
oracle@arrow:hawklas:/tmp
$

RMAN> delete expired archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
5191    1    868     X 2016-JAN-28 11:46:46
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_868_cbnw3vtd_.arc

5201    1    873     X 2016-JAN-28 14:15:44
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_868_cbnw3vtd_.arc RECID=5191 STAMP=902317692
deleted archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc RECID=5201 STAMP=902326632
Deleted 2 EXPIRED objects
RMAN> backup archivelog all;
Starting backup at 2016-JAN-28 14:36:20
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=874 RECID=5203 STAMP=902326725
input archived log thread=1 sequence=875 RECID=5204 STAMP=902326750
channel ORA_DISK_1: starting piece 1 at 2016-JAN-28 14:36:21
channel ORA_DISK_2: starting compressed archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=876 RECID=5207 STAMP=902327780
channel ORA_DISK_2: starting piece 1 at 2016-JAN-28 14:36:22
channel ORA_DISK_1: finished piece 1 at 2016-JAN-28 14:36:23
piece handle=/oradata/backup/HAWK_3130551611_20160128_jgqsgrf5_1_1_1648_1 tag=TAG20160128T143621 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_2: finished piece 1 at 2016-JAN-28 14:36:23
piece handle=/oradata/backup/HAWK_3130551611_20160128_jhqsgrf5_1_1_1649_1 tag=TAG20160128T143621 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-JAN-28 14:36:23

Starting Control File and SPFILE Autobackup at 2016-JAN-28 14:36:23
piece handle=/oradata/backup/HAWK_c-3130551611-20160128-0a comment=NONE
Finished Control File and SPFILE Autobackup at 2016-JAN-28 14:36:30

RMAN>
Updated:

Does this mean we should never perform crosscheck archivelog all? No.

The purpose is to let archivelog backup fail and to investigate.

If investigation shows archivelog is indeed missing, then might be better to perform Level 1 or Level 0 backup.

RMAN> crosscheck archivelog all;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
validation succeeded for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_874_cbo4y4or_.arc RECID=5203 STAMP=902326725
validation succeeded for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_875_cbo4yy69_.arc RECID=5204 STAMP=902326750
validation succeeded for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_876_cbo5z4lz_.arc RECID=5207 STAMP=902327780
Crosschecked 3 objects

validation failed for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_877_cbog0qbg_.arc RECID=5209 STAMP=902336024
Crosschecked 1 objects
RMAN> list expired archivelog all;
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
5209    1    877     X 2016-JAN-28 14:36:20
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_877_cbog0qbg_.arc

RMAN> list backup of archivelog sequence 877;
specification does not match any backup in the repository

RMAN>

Checksum entire directory using md5sum

Wed, 2016-01-27 17:52

When you are backing up 3.5+ TB database, resulting in 600+ GB backupset, transferring to new DC, you would want to ensure there are no corruptions resulting from transfer.

Here is an example of how to perform checkum using md5sum for all the contents in the directory.

Taking the process further, split the .md5 file into multiple files for parallel processing.

oracle@arrow:hawklas:/oradata/keep
$ md5sum *MIG* > backup.md5


oracle@arrow:hawklas:/oradata/keep
$ cat backup.md5
080adb9ba716f3bf6f91efb06adc311e  HAWK_3130551611_20160127_hfqsdvfv_1_1_DB_MIG_1583_1
36bd695b6d87b25d153edc91ee79992f  HAWK_3130551611_20160127_hfqsdvfv_2_1_DB_MIG_1583_2
730c32ef5415bc9dd931d026fb42bcd7  HAWK_3130551611_20160127_hgqsdvg0_1_1_DB_MIG_1584_1
36fd7d0098bd628921ac789155f0a712  HAWK_3130551611_20160127_hgqsdvg0_2_1_DB_MIG_1584_2
072f757dfb808c96aef92555f80165eb  HAWK_3130551611_20160127_hgqsdvg0_3_1_DB_MIG_1584_3
767e06a7eea3cb72243d180a5591e2a2  HAWK_3130551611_20160127_hgqsdvg0_4_1_DB_MIG_1584_4
600ef7710995dcb1d11c0b27a6ee9971  HAWK_3130551611_20160127_hgqsdvg0_5_1_DB_MIG_1584_5
28c279a24cf47fb53b1f7840b7df7fc5  HAWK_3130551611_20160127_hgqsdvg0_6_1_DB_MIG_1584_6
e418ac58da6629f6aeec9c9f7bc47ca5  HAWK_3130551611_20160127_hgqsdvg0_7_1_DB_MIG_1584_7
3e6d8788ec1b5c2071b435c10b34b746  HAWK_3130551611_20160127_hgqsdvg0_8_1_DB_MIG_1584_8
5b1c964eabcc8bd602f6cef15482820a  HAWK_3130551611_20160127_hgqsdvg0_9_1_DB_MIG_1584_9
4655bac6d066ff47799ef8dcf423f532  HAWK_3130551611_20160127_hhqsdvg6_1_1_DB_MIG_1585_1
da4add20652a16726006f46a294cf90a  HAWK_3130551611_20160127_hhqsdvg6_2_1_DB_MIG_1585_2
a2ce4073fb16336c2f8a3cf78f1709ec  HAWK_3130551611_20160127_hhqsdvg6_3_1_DB_MIG_1585_3
301ef428887aba61aa33410d6c8b3c70  HAWK_3130551611_20160127_hhqsdvg6_4_1_DB_MIG_1585_4
47379e228a839bb4257aa141dbfd5107  HAWK_3130551611_20160127_hiqsdvgm_1_1_DB_MIG_1586_1
84e2cd2931f7272832b3c4cd3923e69d  HAWK_3130551611_20160127_hiqsdvgm_2_1_DB_MIG_1586_2
33da63364865b3b959491545905fdc9f  HAWK_3130551611_20160127_hiqsdvgm_3_1_DB_MIG_1586_3
06e04d3e05aff26a197621964fcb8617  HAWK_3130551611_20160127_hiqsdvgm_4_1_DB_MIG_1586_4
5436a855b3d287f9b6ed87ba07cefeb7  HAWK_3130551611_20160127_hiqsdvgm_5_1_DB_MIG_1586_5
0bc71d9930bf2653b6c8661dbf388989  HAWK_3130551611_20160127_hjqsdvh9_1_1_DB_MIG_1587_1
fa2447d3842b476ed365ef37c74db7d9  HAWK_3130551611_20160127_hkqsdvhd_1_1_DB_MIG_1588_1
beb4c1726c99335dff262224828925f5  HAWK_3130551611_20160127_hlqsdvhf_1_1_DB_MIG_1589_1
93aede1cc96dc286ff6c7d927d9505af  HAWK_3130551611_20160127_hmqsdvhh_1_1_DB_MIG_1590_1
eb0a7eb4cab45e5c9b053abb7c736f0d  HAWK_3130551611_20160127_hnqsdvhu_1_1_AL_MIG_1591_1
77808e8a0cb4ac766e7e4d868c7c81b4  HAWK_3130551611_20160127_hoqsdvhu_1_1_AL_MIG_1592_1
5994fe2cf07786495f99d2fd9f42b1f8  HAWK_3130551611_20160127_hpqsdvi5_1_1_AL_MIG_1593_1
784be893cd558cecf65aa51ba79b3e04  HAWK_3130551611_20160127_hpqsdvi5_2_1_AL_MIG_1593_2
5859c37d98d77174675f89e7590ed597  HAWK_3130551611_20160127_hqqsdvi6_1_1_AL_MIG_1594_1
6f2a8a68ab0e9847f8f2248de55cb51a  HAWK_3130551611_20160127_hrqsdvi9_1_1_AL_MIG_1595_1
446976ee788754e8cb48fb00a0acec92  HAWK_3130551611_20160127_hsqsdvid_1_1_AL_MIG_1596_1
73a488bc5aa0664fd80237a2b8da5ea8  HAWK_3130551611_20160127_htqsdvig_1_1_AL_MIG_1597_1
c0200bb23218859fb6a1edc3f7cba94d  HAWK_3130551611_20160127_huqsdvii_1_1_AL_MIG_1598_1
802c8f1524b7c6405d4d41a3b64f0a47  HAWK_3130551611_20160127_hvqsdvil_1_1_AL_MIG_1599_1
acf0c5b5ca6a3f9b58e7880eb093330a  HAWK_3130551611_20160127_i0qsdvil_1_1_AL_MIG_1600_1
dd1746fbaf90b1d867300286e297d2b3  HAWK_3130551611_20160127_i1qsdvin_1_1_AL_MIG_1601_1
7bb58056cac524304a88ba95a6837ac8  HAWK_3130551611_20160127_i2qsdvin_1_1_AL_MIG_1602_1
81ea52aadb6767ac3d3e2ae33acc9d64  HAWK_3130551611_20160127_i3qsdvio_1_1_AL_MIG_1603_1
8481443992c6858edbc03a481e13f579  HAWK_3130551611_20160127_i4qsdvip_1_1_AL_MIG_1604_1
539716837bd98835cf9b43b83cb60b79  HAWK_3130551611_20160127_i5qsdvj0_1_1_CF_MIG_1605_1
d2715ac45c5aa1e7dcd4c706c0a542ee  HAWK_3130551611_20160127_i6qsdvj5_1_1_CF_MIG_1606_1
9532408727adfd012728f989dd9a41ad  HAWK_3130551611_20160127_i7qsdvj8_1_1_CF_MIG_1607_1
840cd94839941643ecd1cbacc568ff9c  HAWK_3130551611_20160127_i8qsdvja_1_1_CF_MIG_1608_1


oracle@arrow:hawklas:/oradata/keep
$ md5sum -c -w backup.md5
HAWK_3130551611_20160127_hfqsdvfv_1_1_DB_MIG_1583_1: OK
HAWK_3130551611_20160127_hfqsdvfv_2_1_DB_MIG_1583_2: OK
HAWK_3130551611_20160127_hgqsdvg0_1_1_DB_MIG_1584_1: OK
HAWK_3130551611_20160127_hgqsdvg0_2_1_DB_MIG_1584_2: OK
HAWK_3130551611_20160127_hgqsdvg0_3_1_DB_MIG_1584_3: OK
HAWK_3130551611_20160127_hgqsdvg0_4_1_DB_MIG_1584_4: OK
HAWK_3130551611_20160127_hgqsdvg0_5_1_DB_MIG_1584_5: OK
HAWK_3130551611_20160127_hgqsdvg0_6_1_DB_MIG_1584_6: OK
HAWK_3130551611_20160127_hgqsdvg0_7_1_DB_MIG_1584_7: OK
HAWK_3130551611_20160127_hgqsdvg0_8_1_DB_MIG_1584_8: OK
HAWK_3130551611_20160127_hgqsdvg0_9_1_DB_MIG_1584_9: OK
HAWK_3130551611_20160127_hhqsdvg6_1_1_DB_MIG_1585_1: OK
HAWK_3130551611_20160127_hhqsdvg6_2_1_DB_MIG_1585_2: OK
HAWK_3130551611_20160127_hhqsdvg6_3_1_DB_MIG_1585_3: OK
HAWK_3130551611_20160127_hhqsdvg6_4_1_DB_MIG_1585_4: OK
HAWK_3130551611_20160127_hiqsdvgm_1_1_DB_MIG_1586_1: OK
HAWK_3130551611_20160127_hiqsdvgm_2_1_DB_MIG_1586_2: OK
HAWK_3130551611_20160127_hiqsdvgm_3_1_DB_MIG_1586_3: OK
HAWK_3130551611_20160127_hiqsdvgm_4_1_DB_MIG_1586_4: OK
HAWK_3130551611_20160127_hiqsdvgm_5_1_DB_MIG_1586_5: OK
HAWK_3130551611_20160127_hjqsdvh9_1_1_DB_MIG_1587_1: OK
HAWK_3130551611_20160127_hkqsdvhd_1_1_DB_MIG_1588_1: OK
HAWK_3130551611_20160127_hlqsdvhf_1_1_DB_MIG_1589_1: OK
HAWK_3130551611_20160127_hmqsdvhh_1_1_DB_MIG_1590_1: OK
HAWK_3130551611_20160127_hnqsdvhu_1_1_AL_MIG_1591_1: OK
HAWK_3130551611_20160127_hoqsdvhu_1_1_AL_MIG_1592_1: OK
HAWK_3130551611_20160127_hpqsdvi5_1_1_AL_MIG_1593_1: OK
HAWK_3130551611_20160127_hpqsdvi5_2_1_AL_MIG_1593_2: OK
HAWK_3130551611_20160127_hqqsdvi6_1_1_AL_MIG_1594_1: OK
HAWK_3130551611_20160127_hrqsdvi9_1_1_AL_MIG_1595_1: OK
HAWK_3130551611_20160127_hsqsdvid_1_1_AL_MIG_1596_1: OK
HAWK_3130551611_20160127_htqsdvig_1_1_AL_MIG_1597_1: OK
HAWK_3130551611_20160127_huqsdvii_1_1_AL_MIG_1598_1: OK
HAWK_3130551611_20160127_hvqsdvil_1_1_AL_MIG_1599_1: OK
HAWK_3130551611_20160127_i0qsdvil_1_1_AL_MIG_1600_1: OK
HAWK_3130551611_20160127_i1qsdvin_1_1_AL_MIG_1601_1: OK
HAWK_3130551611_20160127_i2qsdvin_1_1_AL_MIG_1602_1: OK
HAWK_3130551611_20160127_i3qsdvio_1_1_AL_MIG_1603_1: OK
HAWK_3130551611_20160127_i4qsdvip_1_1_AL_MIG_1604_1: OK
HAWK_3130551611_20160127_i5qsdvj0_1_1_CF_MIG_1605_1: OK
HAWK_3130551611_20160127_i6qsdvj5_1_1_CF_MIG_1606_1: OK
HAWK_3130551611_20160127_i7qsdvj8_1_1_CF_MIG_1607_1: OK
HAWK_3130551611_20160127_i8qsdvja_1_1_CF_MIG_1608_1: OK

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5

oracle@arrow:hawklas:/oradata/keep
$ vi backup.md5  -- create false error by modifying checksum.

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5
HAWK_3130551611_20160127_hfqsdvfv_1_1_DB_MIG_1583_1: FAILED
md5sum: WARNING: 1 of 43 computed checksums did NOT match
oracle@arrow:hawklas:/oradata/keep
$
oracle@arrow:hawklas:/oradata/keep
$ ls *MIG*|wc -l
43

oracle@arrow:hawklas:/oradata/keep
$ split -l 10 backup.md5 backup.md5 -- split file to contain 10 checksums per file.

oracle@arrow:hawklas:/oradata/keep
$ ll ba*
-rw-r--r--. 1 oracle oinstall 3698 Jan 27 12:52 backup.md5
-rw-r--r--. 1 oracle oinstall  860 Jan 27 12:56 backup.md5aa
-rw-r--r--. 1 oracle oinstall  860 Jan 27 12:56 backup.md5ab
-rw-r--r--. 1 oracle oinstall  860 Jan 27 12:56 backup.md5ac
-rw-r--r--. 1 oracle oinstall  860 Jan 27 12:56 backup.md5ad
-rw-r--r--. 1 oracle oinstall  258 Jan 27 12:56 backup.md5ae

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5aa
HAWK_3130551611_20160127_hfqsdvfv_1_1_DB_MIG_1583_1: FAILED
md5sum: WARNING: 1 of 10 computed checksums did NOT match

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5ab

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5ac

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5ad

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5ae

Why use KEEP backup?

Tue, 2016-01-26 19:30

Question which may have been asked.

For 1 time backup to migrate/clone database, KEEP backup does not affect retention policy and not backed up 1 times clause.

Imagine the consequences for deleting 1 off backup which has archivelog backup and the main scripts use not backed up 1-2 times clause which could mean no archivelog for subsequent backups.

RMAN> list archivelog all;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
5067    1    806     A 2016-JAN-21 07:45:48
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_21/o1_mf_1_806_cb1zcp8y_.arc

5068    1    807     A 2016-JAN-21 07:47:01
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_21/o1_mf_1_807_cb1zd4ns_.arc

5070    1    808     A 2016-JAN-21 07:47:16
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_22/o1_mf_1_808_cb4tyo1y_.arc

5071    1    809     A 2016-JAN-22 09:50:11
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_22/o1_mf_1_809_cb4tytsp_.arc

5073    1    810     A 2016-JAN-22 09:50:18
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_26/o1_mf_1_810_cbh5st68_.arc

5072    1    811     A 2016-JAN-26 07:56:40
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_26/o1_mf_1_811_cbh5sz08_.arc

5074    1    812     A 2016-JAN-26 07:56:45
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_26/o1_mf_1_812_cbh7mc9h_.arc


RMAN> backup archivelog sequence 806 KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' tag ARC_KEEP
2> ;

Starting backup at 2016-JAN-26 17:19:57
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=148 device type=DISK
backup will be obsolete on date 2016-FEB-26 17:19:57
archived logs required to recover from this backup will be backed up
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=806 RECID=5067 STAMP=901698422
channel ORA_DISK_1: starting piece 1 at 2016-JAN-26 17:19:58
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/26/2016 17:19:59
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes

RMAN> backup archivelog sequence 806 format '/tmp/U%' KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' tag ARC_KEEP;

Starting backup at 2016-JAN-26 17:20:26
using channel ORA_DISK_1
backup will be obsolete on date 2016-FEB-26 17:20:26
archived logs required to recover from this backup will be backed up
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=806 RECID=5067 STAMP=901698422
channel ORA_DISK_1: starting piece 1 at 2016-JAN-26 17:20:26
channel ORA_DISK_1: finished piece 1 at 2016-JAN-26 17:20:27
piece handle=/tmp/U% tag=ARC_KEEP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-JAN-26 17:20:27

RMAN> backup archivelog sequence 806 not backed up 1 times tag ARC_BKUP;

Starting backup at 2016-JAN-26 17:21:02
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=806 RECID=5067 STAMP=901698422
channel ORA_DISK_1: starting piece 1 at 2016-JAN-26 17:21:02
channel ORA_DISK_1: finished piece 1 at 2016-JAN-26 17:21:03
piece handle=/oradata/fra/HAWKLAS/backupset/2016_01_26/o1_mf_annnn_ARC_BKUP_cbj6vyl7_.bkp tag=ARC_BKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-JAN-26 17:21:03

Starting Control File and SPFILE Autobackup at 2016-JAN-26 17:21:04
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/HAWK_c-3130551611-20160126-02 comment=NONE
Finished Control File and SPFILE Autobackup at 2016-JAN-26 17:21:07

RMAN> backup archivelog sequence 807 tag ARC_BACKUP;

Starting backup at 2016-JAN-26 17:21:30
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=807 RECID=5068 STAMP=901698436
channel ORA_DISK_1: starting piece 1 at 2016-JAN-26 17:21:30
channel ORA_DISK_1: finished piece 1 at 2016-JAN-26 17:21:31
piece handle=/oradata/fra/HAWKLAS/backupset/2016_01_26/o1_mf_annnn_ARC_BACKUP_cbj6wt9y_.bkp tag=ARC_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-JAN-26 17:21:31

Starting Control File and SPFILE Autobackup at 2016-JAN-26 17:21:31
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/HAWK_c-3130551611-20160126-03 comment=NONE
Finished Control File and SPFILE Autobackup at 2016-JAN-26 17:21:34

RMAN> backup archivelog sequence 807 not backed up 1 times;

Starting backup at 2016-JAN-26 17:21:43
using channel ORA_DISK_1
skipping archived log of thread 1 with sequence 807; already backed up
Finished backup at 2016-JAN-26 17:21:43

RMAN> list backup of archivelog all summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
1360    B  A  A DISK        2016-JAN-26 17:20:27 1       1       NO         ARC_KEEP
1361    B  A  A DISK        2016-JAN-26 17:21:02 1       1       NO         ARC_BKUP
1363    B  A  A DISK        2016-JAN-26 17:21:30 1       1       NO         ARC_BACKUP

RMAN> list backup of archivelog sequence 806;


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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
1360    27.00K     DISK        00:00:01     2016-JAN-26 17:20:27
        BP Key: 1505   Status: AVAILABLE  Compressed: NO  Tag: ARC_KEEP
        Piece Name: /tmp/U%

  List of Archived Logs in backup set 1360
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    806     6645495    2016-JAN-21 07:45:48 6645595    2016-JAN-21 07:47:01

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
1361    27.00K     DISK        00:00:00     2016-JAN-26 17:21:02
        BP Key: 1506   Status: AVAILABLE  Compressed: NO  Tag: ARC_BKUP
        Piece Name: /oradata/fra/HAWKLAS/backupset/2016_01_26/o1_mf_annnn_ARC_BKUP_cbj6vyl7_.bkp

  List of Archived Logs in backup set 1361
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    806     6645495    2016-JAN-21 07:45:48 6645595    2016-JAN-21 07:47:01

RMAN> list backup of archivelog sequence 806 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
1360    B  A  A DISK        2016-JAN-26 17:20:27 1       1       NO         ARC_KEEP
1361    B  A  A DISK        2016-JAN-26 17:21:02 1       1       NO         ARC_BKUP

RMAN>

OOPS! Did you see my errors?


Unsolved Case for Missing archived_log Backup

Tue, 2016-01-26 17:00

The project was to migrate database from one DC to another.

The decision we made was to perform RMAN KEEP backup so it does not interfere with existing retention policy.

Backup also resides in its own separate directory for easier checksum and transfer.

This is for 4 nodes RAC environment and backup was taken from node1 at 2016-JAN-21 14:12:10

RMAN backup scripts.
run {
ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
ALLOCATE CHANNEL C3 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
ALLOCATE CHANNEL C4 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
ALLOCATE CHANNEL C5 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
SQL 'ALTER SYSTEM CHECKPOINT';

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE FILESPERSET 1 
KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' TAG='MIGRATION_KEEP';

BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG from time 'trunc(sysdate)' FILESPERSET 2 
KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' TAG='MIGRATION_KEEP';
}
run {
ALLOCATE CHANNEL C6 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/CTL_%d_%I_%T_%U_MIGRATION_%s';
BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' TAG='MIGRATION_KEEP';
}
LIST BACKUP OF DATABASE SUMMARY TAG='MIGRATION_KEEP';
LIST BACKUP OF ARCHIVELOG ALL SUMMARY TAG='MIGRATION_KEEP';
LIST BACKUP OF CONTROLFILE TAG='MIGRATION_KEEP';
REPORT SCHEMA;

When recovering database, we encountered the error below.

ERROR from database recovery
RMAN-06025: no backup of archived log for thread 1 with sequence 287407 and starting SCN of 198452997924 found to restore
According to gv$archived_log, the sequence has not been deleted.
SQL> select inst_id, thread#, sequence#, completion_time, status, deleted
from gv$archived_log
where thread#=1 and sequence# between 287406 and 287408
order by 1,2,3
;

  2    3    4    5  
   INST_ID    THREAD#  SEQUENCE# COMPLETION_TIME      S DEL
---------- ---------- ---------- -------------------- - ---
	 1	    1	  287406 2016-JAN-21 18:51:29 A NO
	 1	    1	  287407 2016-JAN-21 18:59:45 A NO
	 1	    1	  287408 2016-JAN-21 19:00:08 A NO
	 2	    1	  287406 2016-JAN-21 18:51:29 A NO
	 2	    1	  287407 2016-JAN-21 18:59:45 A NO
	 2	    1	  287408 2016-JAN-21 19:00:08 A NO
	 3	    1	  287406 2016-JAN-21 18:51:29 A NO
	 3	    1	  287407 2016-JAN-21 18:59:45 A NO
	 3	    1	  287408 2016-JAN-21 19:00:08 A NO
	 4	    1	  287406 2016-JAN-21 18:51:29 A NO
	 4	    1	  287407 2016-JAN-21 18:59:45 A NO
	 4	    1	  287408 2016-JAN-21 19:00:08 A NO

12 rows selected.

SQL> SQL> 
Backup was started at 2016-JAN-21 14:12:10.

Noticed sequence 287407 thread 1 was missing from the MIGRATION_KEEP backup.

RMAN> list backup of archivelog sequence 287406 thread 1 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233366  B  A  A DISK        2016-JAN-21 18:59:30 1       1       YES        MIGRATION_KEEP
233374  B  A  A DISK        2016-JAN-21 19:23:41 1       1       YES        ARC021THU1923

RMAN> list backup of archivelog sequence 287407 thread 1 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233375  B  A  A DISK        2016-JAN-21 19:23:46 1       1       YES        ARC021THU1923

RMAN> list backup of archivelog sequence 287408 thread 1 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233372  B  A  A DISK        2016-JAN-21 19:00:16 1       1       YES        MIGRATION_KEEP
233377  B  A  A DISK        2016-JAN-21 19:23:47 1       1       YES        ARC021THU1923


RMAN> list backup summary tag MIGRATION_KEEP;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233092  B  0  A DISK        2016-JAN-21 14:12:10 2       1       YES        MIGRATION_KEEP
233093  B  0  A DISK        2016-JAN-21 14:12:19 2       1       YES        MIGRATION_KEEP

233306  B  0  A DISK        2016-JAN-21 18:48:31 1       1       YES        MIGRATION_KEEP
233307  B  0  A DISK        2016-JAN-21 18:48:32 1       1       YES        MIGRATION_KEEP

233308  B  F  A DISK        2016-JAN-21 18:48:37 1       1       YES        MIGRATION_KEEP
233309  B  A  A DISK        2016-JAN-21 18:50:20 1       1       YES        MIGRATION_KEEP
233310  B  A  A DISK        2016-JAN-21 18:50:47 1       1       YES        MIGRATION_KEEP
233311  B  A  A DISK        2016-JAN-21 18:50:48 1       1       YES        MIGRATION_KEEP
233312  B  A  A DISK        2016-JAN-21 18:50:54 1       1       YES        MIGRATION_KEEP
233313  B  A  A DISK        2016-JAN-21 18:50:58 1       1       YES        MIGRATION_KEEP
233314  B  F  A DISK        2016-JAN-21 18:51:12 1       1       YES        MIGRATION_KEEP
233315  B  A  A DISK        2016-JAN-21 18:52:00 1       1       YES        MIGRATION_KEEP

233366  B  A  A DISK        2016-JAN-21 18:59:30 1       1       YES        MIGRATION_KEEP
233367  B  A  A DISK        2016-JAN-21 18:59:32 1       1       YES        MIGRATION_KEEP
233368  B  A  A DISK        2016-JAN-21 18:59:32 1       1       YES        MIGRATION_KEEP
233369  B  A  A DISK        2016-JAN-21 18:59:35 1       1       YES        MIGRATION_KEEP
233370  B  F  A DISK        2016-JAN-21 18:59:54 1       1       YES        MIGRATION_KEEP
233371  B  F  A DISK        2016-JAN-21 19:00:04 1       1       YES        MIGRATION_KEEP
233372  B  A  A DISK        2016-JAN-21 19:00:16 1       1       YES        MIGRATION_KEEP
233373  B  F  A DISK        2016-JAN-21 19:00:22 1       1       YES        MIGRATION_KEEP

RMAN> list backup of controlfile summary tag MIGRATION_KEEP;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233314  B  F  A DISK        2016-JAN-21 18:51:12 1       1       YES        MIGRATION_KEEP
233370  B  F  A DISK        2016-JAN-21 18:59:54 1       1       YES        MIGRATION_KEEP
233373  B  F  A DISK        2016-JAN-21 19:00:22 1       1       YES        MIGRATION_KEEP --- This CF was restored.

RMAN> 
RMAN> restore controlfile from '/rman_bkp/FRA/migration_backup/CTL_3036635614_20160121_m6qrusa4_1_1_MIGRATION_235206';
RMAN> list backup of archivelog all summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233309  B  A  A DISK        21-JAN-2016 18:50:20 1       1       YES        MIGRATION_KEEP

233365  B  A  A DISK        21-JAN-2016 18:59:29 1       1       YES        MIGRATION_KEEP
233366  B  A  A DISK        21-JAN-2016 18:59:30 1       1       YES        MIGRATION_KEEP
233367  B  A  A DISK        21-JAN-2016 18:59:32 1       1       YES        MIGRATION_KEEP
233368  B  A  A DISK        21-JAN-2016 18:59:32 1       1       YES        MIGRATION_KEEP
233369  B  A  A DISK        21-JAN-2016 18:59:35 1       1       YES        MIGRATION_KEEP
233372  B  A  A DISK        21-JAN-2016 19:00:16 1       1       YES        MIGRATION_KEEP

RMAN> list backupset 233372;


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


BS Key  Size       Device Type Elapsed Time Completion Time     
------- ---------- ----------- ------------ --------------------
233372  35.84M     DISK        00:00:04     21-JAN-2016 19:00:16
        BP Key: 359665   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION_KEEP
        Piece Name: /rman_bkp/FRA/migration_backup/CTL_3036635614_20160121_m5qrus9s_1_1_MIGRATION_235205
        Keep: BACKUP_LOGS        Until: 21-FEB-2016 19:00:12

  List of Archived Logs in backup set 233372
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    287408  198453187859 21-JAN-2016 18:59:44 198453194240 21-JAN-2016 19:00:08
  2    207046  198452998035 21-JAN-2016 18:51:29 198453187879 21-JAN-2016 18:59:44
  2    207047  198453187879 21-JAN-2016 18:59:44 198453193569 21-JAN-2016 19:00:05
  3    182524  198452999167 21-JAN-2016 18:51:31 198453188295 21-JAN-2016 18:59:47
  3    182525  198453188295 21-JAN-2016 18:59:47 198453194175 21-JAN-2016 19:00:08
  4    75721   198452999243 21-JAN-2016 18:51:32 198453188286 21-JAN-2016 18:59:47
  4    75722   198453188286 21-JAN-2016 18:59:47 198453194112 21-JAN-2016 19:00:08

RMAN> 
Even from the log file sequence 287407 is missing.
channel C4: backup set complete, elapsed time: 00:00:30
channel C4: starting compressed archived log backup set
channel C4: specifying archived log(s) in backup set
input archived log thread=4 sequence=75720 RECID=709008 STAMP=901738292
input archived log thread=1 sequence=287406 RECID=709005 STAMP=901738289
channel C4: starting piece 1 at 2016-JAN-21 18:59:28
channel C5: finished piece 1 at 2016-JAN-21 18:59:28
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_lvqrus7u_1_1_MIGRATION_235199 tag=MIGRATION_KEEP comment=NONE
channel C5: backup set complete, elapsed time: 00:00:13
channel C5: starting compressed archived log backup set
channel C5: specifying archived log(s) in backup set
input archived log thread=2 sequence=207045 RECID=709006 STAMP=901738289
channel C5: starting piece 1 at 2016-JAN-21 18:59:29
channel C3: finished piece 1 at 2016-JAN-21 18:59:30
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_luqrus7p_1_1_MIGRATION_235198 tag=MIGRATION_KEEP comment=NONE
channel C3: backup set complete, elapsed time: 00:00:20
channel C4: finished piece 1 at 2016-JAN-21 18:59:32
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_m1qrus8g_1_1_MIGRATION_235201 tag=MIGRATION_KEEP comment=NONE
channel C4: backup set complete, elapsed time: 00:00:04
channel C5: finished piece 1 at 2016-JAN-21 18:59:32
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_m2qrus8g_1_1_MIGRATION_235202 tag=MIGRATION_KEEP comment=NONE
channel C5: backup set complete, elapsed time: 00:00:03
channel C1: finished piece 1 at 2016-JAN-21 18:59:36
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_ltqrus7p_1_1_MIGRATION_235197 tag=MIGRATION_KEEP comment=NONE
channel C1: backup set complete, elapsed time: 00:00:31
channel C2: finished piece 1 at 2016-JAN-21 18:59:36
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_m0qrus83_1_1_MIGRATION_235200 tag=MIGRATION_KEEP comment=NONE
channel C2: backup set complete, elapsed time: 00:00:15
Finished backup at 2016-JAN-21 18:59:36
released channel: C1
released channel: C2
released channel: C3
released channel: C4
released channel: C5
                               
allocated channel: C6
channel C6: SID=373 instance=1 device type=DISK

Starting backup at 2016-JAN-21 18:59:44
current log archived

backup will be obsolete on date 2016-FEB-21 18:59:52
archived logs required to recover from this backup will be backed up
channel C6: starting compressed full datafile backup set
channel C6: specifying datafile(s) in backup set
including current control file in backup set
channel C6: starting piece 1 at 2016-JAN-21 18:59:53
channel C6: finished piece 1 at 2016-JAN-21 19:00:04
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m3qrus98_1_1_MIGRATION_235203 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:11

backup will be obsolete on date 2016-FEB-21 19:00:04
archived logs required to recover from this backup will be backed up
channel C6: starting compressed full datafile backup set
channel C6: specifying datafile(s) in backup set
including current SPFILE in backup set
channel C6: starting piece 1 at 2016-JAN-21 19:00:04
channel C6: finished piece 1 at 2016-JAN-21 19:00:05
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m4qrus9k_1_1_MIGRATION_235204 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:01

backup will be obsolete on date 2016-FEB-21 19:00:04
archived logs required to recover from this backup will be backed up
channel C6: starting compressed full datafile backup set
channel C6: specifying datafile(s) in backup set
including current SPFILE in backup set
channel C6: starting piece 1 at 2016-JAN-21 19:00:04
channel C6: finished piece 1 at 2016-JAN-21 19:00:05
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m4qrus9k_1_1_MIGRATION_235204 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:01

current log archived
backup will be obsolete on date 2016-FEB-21 19:00:12
archived logs required to recover from this backup will be backed up
channel C6: starting compressed archived log backup set
channel C6: specifying archived log(s) in backup set
input archived log thread=2 sequence=207046 RECID=709010 STAMP=901738785
input archived log thread=3 sequence=182524 RECID=709011 STAMP=901738788
input archived log thread=4 sequence=75721 RECID=709012 STAMP=901738788
input archived log thread=1 sequence=287408 RECID=709016 STAMP=901738808
input archived log thread=2 sequence=207047 RECID=709013 STAMP=901738806
input archived log thread=4 sequence=75722 RECID=709014 STAMP=901738808
input archived log thread=3 sequence=182525 RECID=709015 STAMP=901738808
channel C6: starting piece 1 at 2016-JAN-21 19:00:13
channel C6: finished piece 1 at 2016-JAN-21 19:00:20
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m5qrus9s_1_1_MIGRATION_235205 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:07

backup will be obsolete on date 2016-FEB-21 19:00:20
archived logs required to recover from this backup will be backed up
channel C6: starting compressed full datafile backup set
channel C6: specifying datafile(s) in backup set
including current control file in backup set
channel C6: starting piece 1 at 2016-JAN-21 19:00:21
channel C6: finished piece 1 at 2016-JAN-21 19:00:31
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m6qrusa4_1_1_MIGRATION_235206 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:10
Finished backup at 2016-JAN-21 19:00:31
released channel: C6
Any ideas as to why the archived log was missing from backup?

BTW, I have already deleted the backups to save space.


Validate DG Broker Config for Switchover

Sat, 2016-01-16 12:08

Primary and Standby databases are running on the same server using OMF with listening on port 1530/1531

Note I have – TraceLevel = ‘SUPPORT’

+++ Check listener for DGMGRL service from PRIMARY and STANDBY.
oracle@arrow:hawksan:/media/sf_working/dataguard
$ lsnrctl status listener_las|grep DG -A 1
Service "hawklas_DGB" has 1 instance(s).
  Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas_DGMGRL" has 1 instance(s).
  Instance "hawklas", status UNKNOWN, has 1 handler(s) for this service...

oracle@arrow:hawksan:/media/sf_working/dataguard
$ lsnrctl status listener_san|grep DG -A 1
Service "hawksan_DGB" has 1 instance(s).
  Instance "hawksan", status READY, has 1 handler(s) for this service...
Service "hawksan_DGMGRL" has 1 instance(s).
  Instance "hawksan", status UNKNOWN, has 1 handler(s) for this service...

Get into habit of using instance versus database where applicable for RAC compatibility.

DGMGRL> show database hawklas

Database - hawklas

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

Database Status:
SUCCESS

DGMGRL> show database hawklas DGConnectIdentifier
  DGConnectIdentifier = 'hawklas'
DGMGRL> show instance hawklas DGConnectIdentifier
  DGConnectIdentifier = 'hawklas'
DGMGRL>
+++ Check DG Configuration
oracle@arrow:hawklas:/media/sf_working/dataguard
$ ./check_dg.sh
***** Checking Data Guard Broker Configuration ....
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration verbose

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show configuration TraceLevel
  TraceLevel = 'SUPPORT'
DGMGRL> show database hawklas

Database - hawklas

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

Database Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      45.00 KByte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> show instance hawklas DGConnectIdentifier
  DGConnectIdentifier = 'hawklas'
DGMGRL> show instance hawksan DGConnectIdentifier
  DGConnectIdentifier = 'hawksan'
DGMGRL> show instance hawklas StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=arrow)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGMGRL)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))'
DGMGRL> show instance hawksan StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=arrow)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGMGRL)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))'
DGMGRL> show instance hawklas InconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL> show instance hawksan InconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL> show instance hawklas LogArchiveMaxProcesses
  LogArchiveMaxProcesses = '4'
DGMGRL> show instance hawksan LogArchiveMaxProcesses
  LogArchiveMaxProcesses = '4'
DGMGRL> show instance hawklas DelayMins
  DelayMins = '0'
DGMGRL> show instance hawksan DelayMins
  DelayMins = '0'
DGMGRL> show instance hawklas LogArchiveTrace
  LogArchiveTrace = '0'
DGMGRL> show instance hawksan LogArchiveTrace
  LogArchiveTrace = '0'
DGMGRL> show instance hawklas statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show instance hawksan statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> exit
oracle@arrow:hawklas:/media/sf_working/dataguard
$
+++ Test connectivity to database using StaticConnectIdentifier from DG Broker
oracle@arrow:hawksan:/media/sf_working/dataguard
$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 16 08:10:31 2016

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

@> connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=arrow)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGMGRL)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))' as sysdba
Connected.
ARROW:(SYS@hawklas):PRIMARY> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      hawk
db_unique_name                       string      hawklas
global_names                         boolean     FALSE
instance_name                        string      hawklas
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      hawk,hawklas
ARROW:(SYS@hawklas):PRIMARY> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

ARROW:(SYS@hawklas):PRIMARY> connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=arrow)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGMGRL)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))' as sysdba
Connected.
ARROW:(SYS@hawksan):PHYSICAL STANDBY> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      hawk
db_unique_name                       string      hawksan
global_names                         boolean     FALSE
instance_name                        string      hawksan
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      hawk,hawksan
ARROW:(SYS@hawksan):PHYSICAL STANDBY> select open_mode, database_role from v$database;

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

ARROW:(SYS@hawksan):PHYSICAL STANDBY>
+++ switchover to hawksan (STANDBY)

Must connect as sys@tns (typically same as DGConnectIdentifier) for switchover.

oracle@arrow:hawklas:/media/sf_working/dataguard
$ ./clearlog.sh
oracle@arrow:hawklas:/media/sf_working/dataguard
$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@hawklas
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawklas

Database - hawklas

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

Database Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      19.00 KByte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> switchover to hawksan
Performing switchover NOW, please wait...
Operation requires a connection to instance "hawksan" on database "hawksan"
Connecting to instance "hawksan"...
Connected.
New primary database "hawksan" is opening...
Operation requires startup of instance "hawklas" on database "hawklas"
Starting instance "hawklas"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "hawksan"
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawksan - Primary database
    hawklas - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

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

Database Status:
SUCCESS

DGMGRL> show database hawklas

Database - hawklas

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    hawklas

Database Status:
SUCCESS

DGMGRL> exit
++++ Save logs for reference
oracle@arrow:hawklas:/media/sf_working/dataguard
$ ./savelog.sh
`/u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_las.log' -> `/tmp/listener_las.log'
`/u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_san.log' -> `/tmp/listener_san.log'
`/u01/app/oracle/diag/rdbms/hawklas/hawklas/trace/alert_hawklas.log' -> `/tmp/alert_hawklas.log'
`/u01/app/oracle/diag/rdbms/hawklas/hawklas/trace/drchawklas.log' -> `/tmp/drchawklas.log'
`/u01/app/oracle/diag/rdbms/hawksan/hawksan/trace/alert_hawksan.log' -> `/tmp/alert_hawksan.log'
`/u01/app/oracle/diag/rdbms/hawksan/hawksan/trace/drchawksan.log' -> `/tmp/drchawksan.log'
+++ switchover to hawklas (STANDBY)

Must connect as sys@tns (typically same as DGConnectIdentifier) for switchover.

oracle@arrow:hawklas:/media/sf_working/dataguard
$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@hawklas
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawksan - Primary database
    hawklas - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to hawklas
Performing switchover NOW, please wait...
New primary database "hawklas" is opening...
Operation requires startup of instance "hawksan" on database "hawksan"
Starting instance "hawksan"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "hawklas"
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawklas

Database - hawklas

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

Database Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> exit
oracle@arrow:hawklas:/media/sf_working/dataguard
$

ORA-12514: TNS:listener during switchover using DGMGRL

Sun, 2016-01-10 20:02

Not sure what I am doing wrong because DEFAULT StaticConnectIdentifier does not seem to work.

Google and MOS were not much help.

Using StaticConnectIdentifier as created by dmgrl would fail with the following error during switchover.
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

The error can be found in the listener.log

When using tnsnames for StaticConnectIdentifier, switchover works like a charm.

I keep thinking, there must be something wrong with my configuration but as you can see from the test case, the only changes were to StaticConnectIdentifier.

Both databases are on the same host with each listening on different ports.

Updated: Just found this note
DGMGRL>switchover to Fails with ORA-12514 (Doc ID 1582927.1)
Oracle Database – Enterprise Edition – Version 12.1.0.1 to 12.1.0.1 [Release 12.1]

Interesting to see if this will work.

oracle@arrow:hawklas:/home/oracle
$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> connect sys/oracle@dc_las
Connected.
DGMGRL> connect sys/oracle@dc_san
Connected.
DGMGRL> show configuration verbose

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database verbose hawklas

Database - hawklas

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

  Properties:
    DGConnectIdentifier             = 'dc_las'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '1800'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'hawklas'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGMGRL)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '8191'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL> show database verbose hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      32.00 KByte/s
  Real Time Query: ON
  Instance(s):
    hawksan

  Properties:
    DGConnectIdentifier             = 'dc_san'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '30'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '1800'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'hawksan'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGMGRL)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '8191'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      32.00 KByte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS
DGMGRL> connect sys/oracle@dc_las
Connected.
DGMGRL> switchover to hawksan
Performing switchover NOW, please wait...
Operation requires a connection to instance "hawksan" on database "hawksan"
Connecting to instance "hawksan"...
Connected.
New primary database "hawksan" is opening...
Operation requires startup of instance "hawklas" on database "hawklas"
Starting instance "hawklas"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
        start up instance "hawklas" of database "hawklas"

DGMGRL> exit
oracle@arrow:hawklas:/home/oracle
$ sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 17:36:20 2016

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

Connected to an idle instance.

SYS@hawklas> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             385876872 bytes
Database Buffers          675282944 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.
SYS@hawklas> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawklas:/home/oracle
$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawksan - Primary database
    hawklas - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-16534: switchover, failover or convert operation in progress
DGM-17017: unable to determine configuration status

DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawksan - Primary database
    hawklas - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> edit database hawksan set property StaticConnectIdentifier='dc_san';
Property "staticconnectidentifier" updated
DGMGRL> edit database hawklas set property StaticConnectIdentifier='dc_las';
Property "staticconnectidentifier" updated'
DGMGRL> connect sys/oracle@dc_san
Connected.
DGMGRL> switchover to hawklas
Performing switchover NOW, please wait...
Operation requires a connection to instance "hawklas" on database "hawklas"
Connecting to instance "hawklas"...
Connected.
New primary database "hawklas" is opening...
Operation requires startup of instance "hawksan" on database "hawksan"
Starting instance "hawksan"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "hawklas"
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> exit
oracle@arrow:hawklas:/home/oracle
$
oracle@arrow:hawklas:/home/oracle
$ tns
oracle@arrow:hawklas:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$ tnsping dc_las

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 10-JAN-2016 17:50:08

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

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


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (SDU=32767) (SEND_BUF_SIZE=32767) (RECV_BUF_SIZE=32767) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = arrow)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = hawklas)))
OK (0 msec)
oracle@arrow:hawklas:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$ tnsping dc_san

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 10-JAN-2016 17:50:14

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

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


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (SDU=32767) (SEND_BUF_SIZE=32767) (RECV_BUF_SIZE=32767) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = arrow)(PORT = 1531))) (CONNECT_DATA = (SERVICE_NAME = hawksan)))
OK (0 msec)
oracle@arrow:hawklas:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$ lsnrctl status listener_las

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-JAN-2016 17:50:26

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow)(PORT=1521)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767)))
STATUS of the LISTENER
------------------------
Alias                     listener_las
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                10-JAN-2016 16:15:51
Uptime                    0 days 1 hr. 34 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_las.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521)))
Services Summary...
Service "foo2" has 1 instance(s).
  Instance "foo2", status UNKNOWN, has 1 handler(s) for this service...
Service "hawk" has 1 instance(s).
  Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas" has 2 instance(s).
  Instance "hawklas", status UNKNOWN, has 2 handler(s) for this service...
  Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas_DGB" has 1 instance(s).
  Instance "hawklas", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@arrow:hawklas:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$ lsnrctl status listener_san

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-JAN-2016 17:50:29

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow)(PORT=1531)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767)))
STATUS of the LISTENER
------------------------
Alias                     listener_san
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                10-JAN-2016 16:15:54
Uptime                    0 days 1 hr. 34 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_san.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531)))
Services Summary...
Service "hawk" has 1 instance(s).
  Instance "hawksan", status READY, has 1 handler(s) for this service...
Service "hawksan" has 2 instance(s).
  Instance "hawksan", status UNKNOWN, has 2 handler(s) for this service...
  Instance "hawksan", status READY, has 1 handler(s) for this service...
Service "hawksan_DGB" has 1 instance(s).
  Instance "hawksan", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@arrow:hawklas:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$

It seems that static registration is not working for db_unique_name_DMGRL

oracle@arrow:hawksan:/home/oracle
$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show database hawklas StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGMGRL)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))'
DGMGRL> show database hawksan StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGMGRL)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))'
DGMGRL> exit
oracle@arrow:hawksan:/home/oracle
$ lsnrctl status listener_las

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-JAN-2016 22:16:09

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767)))
STATUS of the LISTENER
------------------------
Alias                     listener_las
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                10-JAN-2016 22:02:05
Uptime                    0 days 0 hr. 14 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_las.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521)))
Services Summary...
Service "foo2" has 1 instance(s).
  Instance "foo2", status UNKNOWN, has 1 handler(s) for this service...
Service "hawk" has 1 instance(s).
  Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas" has 2 instance(s).
  Instance "hawklas", status UNKNOWN, has 2 handler(s) for this service...
  Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas_DGB" has 1 instance(s).
  Instance "hawklas", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@arrow:hawksan:/home/oracle
$ lsnrctl status listener_san

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-JAN-2016 22:16:12

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767)))
STATUS of the LISTENER
------------------------
Alias                     listener_san
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                10-JAN-2016 22:02:03
Uptime                    0 days 0 hr. 14 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_san.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531)))
Services Summary...
Service "hawk" has 1 instance(s).
  Instance "hawksan", status READY, has 1 handler(s) for this service...
Service "hawksan" has 2 instance(s).
  Instance "hawksan", status UNKNOWN, has 2 handler(s) for this service...
  Instance "hawksan", status READY, has 1 handler(s) for this service...
Service "hawksan_DGB" has 1 instance(s).
  Instance "hawksan", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@arrow:hawksan:/home/oracle
$ sqlplus sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGMGRL)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))' as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 22:17:09 2016

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

Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name: ^C
oracle@arrow:hawksan:/home/oracle
$ sqlplus sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGMGRL)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))' as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 22:17:20 2016

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

Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name: ^C
oracle@arrow:hawksan:/home/oracle
$ sqlplus sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGB)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))' as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 22:17:48 2016

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawksan:/home/oracle
$ sqlplus sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGB)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))' as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 22:17:55 2016

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@hawksan):PHYSICAL STANDBY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawksan:/home/oracle
$

$ tns
oracle@arrow:hawksan:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$ grep -i dg listener.ora
        (GLOBAL_NAME = hawklas_DGB)
        (GLOBAL_NAME = hawklas_DGMGRL)
        (GLOBAL_NAME = hawksan_DGB)
        (GLOBAL_NAME = hawksan_DGMGRL)
oracle@arrow:hawksan:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$

UPDATED Jan 16, 2015.

Thanks to Shivananda Rao (http://shivanandarao-oracle.com/) for pointing out the obvious which I was blind to.

IT’S NOT GLOBAL_NAME = hawklas_DGMGRL !!!

It’s GLOBAL_DBNAME = hawklas_DGMGRL (Missing DB)

R.I.P.

 


How Reliable is v$archive_dest_status?

Sun, 2016-01-10 13:23

v$archive_dest_status

Any DG Experts out there who knows more about the reliability for v$archive_dest_status as it has been many years since I have written scripts to monitor DG.

Don’t want to reinvent the wheel if I cannot make it better.

Note: gap_status is only available from 11gR2 +++

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 11:06:55 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@hawklas):PRIMARY> select
  2    arch.dest_id,
  3    arch.thread# "thread",
  4    arch.sequence# "last sequence received",
  5    appl.sequence# "last sequence applied",
  6   (arch.sequence#-appl.sequence#) "difference"
  7  from
  8    (
  9      select dest_id,thread#,sequence# from v$archived_log
 10      where (thread#,first_time)
 11      in (select thread#,max(first_time) from v$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#)
 12    ) arch,
 13    (
 14      select thread# ,sequence# from v$log_history
 15      where (thread#,first_time)
 16      in (select thread#,max(first_time) from v$log_history where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#)
 17    ) appl
 18  where arch.thread#=appl.thread#
 19  order by 1
 20  ;

DEST_ID thread last sequence received last sequence applied difference
------- ------ ---------------------- --------------------- ----------
      1      1                    234                   234          0
      2      1                    234                   234          0

Elapsed: 00:00:07.99
ARROW:(SYS@hawklas):PRIMARY> select
  2  DEST_ID,STATUS,TYPE,DATABASE_MODE,RECOVERY_MODE,STANDBY_LOGFILE_COUNT srl_ct,STANDBY_LOGFILE_ACTIVE srl_active,
  3  ARCHIVED_THREAD# thrd,ARCHIVED_SEQ# seq,APPLIED_SEQ# applied,SRL,
  4  GAP_STATUS, decode(ERROR,null,'NONE','ERROR') error
  5  from v$archive_dest_status
  6  where status!='INACTIVE'
  7  ;

DEST_ID STATUS    TYPE           DATABASE_MODE   RECOVERY_MODE           SRL_CT SRL_ACTIVE THRD  SEQ APPLIED SRL GAP_STATUS               ERROR
------- --------- -------------- --------------- ----------------------- ------ ---------- ---- ---- ------- --- ------------------------ -----
      1 VALID     LOCAL          OPEN            IDLE                         0          0    1  234       0 NO                           NONE
      2 VALID     PHYSICAL       MOUNTED-STANDBY MANAGED REAL TIME APPLY      4          0    1  234     233 YES NO GAP                   NONE

Elapsed: 00:00:00.01
ARROW:(SYS@hawklas):PRIMARY>
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 11:06:36 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@hawksan):PHYSICAL STANDBY> select
  2    arch.dest_id,
  3    arch.thread# "thread",
  4    arch.sequence# "last sequence received",
  5    appl.sequence# "last sequence applied",
  6   (arch.sequence#-appl.sequence#) "difference"
  7  from
  8    (
  9      select dest_id,thread#,sequence# from v$archived_log
 10      where (thread#,first_time)
 11      in (select thread#,max(first_time) from v$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#)
 12    ) arch,
 13    (
 14      select thread# ,sequence# from v$log_history
 15      where (thread#,first_time)
 16      in (select thread#,max(first_time) from v$log_history where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#)
 17    ) appl
 18  where arch.thread#=appl.thread#
 19  order by 1
 20  ;

DEST_ID thread last sequence received last sequence applied difference
------- ------ ---------------------- --------------------- ----------
      1      1                    234                   234          0

Elapsed: 00:00:00.01
ARROW:(SYS@hawksan):PHYSICAL STANDBY> select
  2  DEST_ID,STATUS,TYPE,DATABASE_MODE,RECOVERY_MODE,STANDBY_LOGFILE_COUNT srl_ct,STANDBY_LOGFILE_ACTIVE srl_active,
  3  ARCHIVED_THREAD# thrd,ARCHIVED_SEQ# seq,APPLIED_SEQ# applied,SRL,
  4  GAP_STATUS, decode(ERROR,null,'NONE','ERROR') error
  5  from v$archive_dest_status
  6  where status!='INACTIVE'
  7  ;

DEST_ID STATUS    TYPE           DATABASE_MODE   RECOVERY_MODE           SRL_CT SRL_ACTIVE THRD  SEQ APPLIED SRL GAP_STATUS               ERROR
------- --------- -------------- --------------- ----------------------- ------ ---------- ---- ---- ------- --- ------------------------ -----
      1 VALID     LOCAL          MOUNTED-STANDBY MANAGED REAL TIME APPLY      0          0    1  234       0 NO                           NONE
      2 VALID     UNKNOWN        UNKNOWN         IDLE                         0          0    0    0       0 NO                           NONE
     32 VALID     UNKNOWN        UNKNOWN         IDLE                         0          0    1  234     234 NO                           NONE

Elapsed: 00:00:00.00
ARROW:(SYS@hawksan):PHYSICAL STANDBY>

ORA-01652: unable to extend temp segment even with Autoextend ON

Wed, 2016-01-06 18:22

I have encountered a very strange feature and wondering if anyone has experienced this.

Tracing was enabled for “ORA-01652: unable to extend temp segment” using alter system set events ‘1652 trace name errorstack level 1’;.

The tablespace is configured to autotextend by 64MB with max size 31GB and currently has 4MB Free.

“create index x on abc(txt) tablespace users;” failed with ORA-01652: unable to extend temp segment by 128 in tablespace USERS.

I understand objects are first created as temporary segments in USERS tablespace and this does not occur if there is no tracing enable for event 1652.

Any ideas or explanations?

ARROW:(MDINH@hawklas):PRIMARY> alter database datafile 4 autoextend on next 64m;

Database altered.

ARROW:(MDINH@hawklas):PRIMARY> @check_tbs.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 echo on
ARROW:(MDINH@hawklas):PRIMARY> col file_name for a55
ARROW:(MDINH@hawklas):PRIMARY> col segment_name for a30
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a30
ARROW:(MDINH@hawklas):PRIMARY> col segment_type for a18
ARROW:(MDINH@hawklas):PRIMARY> select extent_management, allocation_type, segment_space_management,
  2  initial_extent, next_extent, pct_increase
  3  from dba_tablespaces
  4  where tablespace_name=UPPER('&&tbs')
  5  ;

EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
---------- --------- ------ -------------- ----------- ------------
LOCAL      SYSTEM    AUTO            65536

ARROW:(MDINH@hawklas):PRIMARY> select tablespace_name, file_id, file_name, bytes/1024/1024 mb, autoextensible, maxbytes/1024/1024/1024 max_gb
  2  from dba_data_files
  3  where tablespace_name=UPPER('&&tbs')
  4  ;

TABLESPACE_NAME                   FILE_ID FILE_NAME                                                       MB AUT     MAX_GB
------------------------------ ---------- ------------------------------------------------------- ---------- --- ----------
USERS                                   4 /oradata/HAWKLAS/datafile/o1_mf_users_c7120bbr_.dbf            160 YES 31.9999847

ARROW:(MDINH@hawklas):PRIMARY> select max(bytes)/1024/1024 max_free_mb, sum(bytes)/1024/1024 total_free_mb, count(*)
  2  from dba_free_space
  3  where tablespace_name=UPPER('&&tbs')
  4  ;

MAX_FREE_MB TOTAL_FREE_MB   COUNT(*)
----------- ------------- ----------
          4             6          7

ARROW:(MDINH@hawklas):PRIMARY> select segment_type, segment_subtype, max(initial_extent), max(next_extent)
  2  from dba_segments
  3  where tablespace_name=UPPER('&&tbs')
  4  group by segment_type, segment_subtype
  5  ;

SEGMENT_TYPE       SEGMENT_SU MAX(INITIAL_EXTENT) MAX(NEXT_EXTENT)
------------------ ---------- ------------------- ----------------
INDEX              ASSM                     65536          1048576
LOBSEGMENT         ASSM                     65536          1048576
LOBINDEX           ASSM                     65536          1048576
TABLE              ASSM                  94371840          1048576

ARROW:(MDINH@hawklas):PRIMARY> ALTER SESSION SET TRACEFILE_IDENTIFIER = "mdinh_test2";

Session altered.

ARROW:(MDINH@hawklas):PRIMARY> alter system set events '1652 trace name errorstack level 1';

System altered.

ARROW:(MDINH@hawklas):PRIMARY> create index x on abc(txt) tablespace users;
^Ccreate index x on abc(txt) tablespace users
                  *
ERROR at line 1:
ORA-01013: user requested cancel of current operation



ARROW:(MDINH@hawklas):PRIMARY> alter database datafile
  2   '/oradata/HAWKLAS/datafile/o1_mf_users_c7120bbr_.dbf' resize 160m;

Database altered.

ARROW:(MDINH@hawklas):PRIMARY>
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/hawklas/hawklas/trace
$ tail -20  alert_hawklas.log
  Current log# 3 seq# 75 mem# 0: /oradata/HAWKLAS/onlinelog/o1_mf_3_c7129gc8_.log
  Current log# 3 seq# 75 mem# 1: /oradata/fra/HAWKLAS/onlinelog/o1_mf_3_c7129go4_.log
Wed Jan 06 15:27:16 2016
Archived Log entry 4037 added for thread 1 sequence 74 ID 0xbc2f7b69 dest 1:
Wed Jan 06 15:46:41 2016
OS Pid: 3999 executed alter system set events '1652 trace name errorstack level 1'
Wed Jan 06 15:47:08 2016
Errors in file /u01/app/oracle/diag/rdbms/hawklas/hawklas/trace/hawklas_ora_3999_mdinh_test2.trc:
ORA-01652: unable to extend temp segment by 128 in tablespace USERS
Wed Jan 06 15:50:09 2016
Thread 1 advanced to log sequence 76 (LGWR switch)
  Current log# 1 seq# 76 mem# 0: /oradata/HAWKLAS/onlinelog/o1_mf_1_c7128lk6_.log
  Current log# 1 seq# 76 mem# 1: /oradata/fra/HAWKLAS/onlinelog/o1_mf_1_c7128lvc_.log
Wed Jan 06 15:50:16 2016
Archived Log entry 4038 added for thread 1 sequence 75 ID 0xbc2f7b69 dest 1:
Wed Jan 06 15:51:01 2016
alter database datafile
 '/oradata/HAWKLAS/datafile/o1_mf_users_c7120bbr_.dbf' resize 160m
Completed: alter database datafile
 '/oradata/HAWKLAS/datafile/o1_mf_users_c7120bbr_.dbf' resize 160m
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/hawklas/hawklas/trace
$

Updated Jan 07, 2016 based on comments.

There are sufficient space from the OS.
Size of the table is 96MB and size of the created index is 104MB

From the alert log, the ORA-01652 did not occur if tracing is turned off for the event – alter system set events ‘1652 trace name errorstack off’;

ARROW:(MDINH@hawklas):PRIMARY> @check_tbs.sql
ARROW:(MDINH@hawklas):PRIMARY> col file_name for a55
ARROW:(MDINH@hawklas):PRIMARY> col segment_name for a30
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a30
ARROW:(MDINH@hawklas):PRIMARY> col segment_type for a18
ARROW:(MDINH@hawklas):PRIMARY> select extent_management, allocation_type, segment_space_management,
  2  initial_extent, next_extent, pct_increase
  3  from dba_tablespaces
  4  where tablespace_name=UPPER('&&tbs')
  5  ;
Enter value for tbs: users

EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
---------- --------- ------ -------------- ----------- ------------
LOCAL      SYSTEM    AUTO            65536

ARROW:(MDINH@hawklas):PRIMARY> select tablespace_name, file_id, file_name, bytes/1024/1024 mb, autoextensible, maxbytes/1024/1024/1024 max_gb
  2  from dba_data_files
  3  where tablespace_name=UPPER('&&tbs')
  4  ;

TABLESPACE_NAME                   FILE_ID FILE_NAME                                                       MB AUT     MAX_GB
------------------------------ ---------- ------------------------------------------------------- ---------- --- ----------
USERS                                   4 /oradata/HAWKLAS/datafile/o1_mf_users_c7120bbr_.dbf            160 YES 31.9999847

ARROW:(MDINH@hawklas):PRIMARY> select max(bytes)/1024/1024 max_free_mb, sum(bytes)/1024/1024 total_free_mb, count(*)
  2  from dba_free_space
  3  where tablespace_name=UPPER('&&tbs')
  4  ;

MAX_FREE_MB TOTAL_FREE_MB   COUNT(*)
----------- ------------- ----------
          4             6          7

ARROW:(MDINH@hawklas):PRIMARY> select segment_type, segment_subtype, max(initial_extent), max(next_extent)
  2  from dba_segments
  3  where tablespace_name=UPPER('&&tbs')
  4  group by segment_type, segment_subtype
  5  ;

SEGMENT_TYPE       SEGMENT_SU MAX(INITIAL_EXTENT) MAX(NEXT_EXTENT)
------------------ ---------- ------------------- ----------------
INDEX              ASSM                     65536          1048576
LOBSEGMENT         ASSM                     65536          1048576
LOBINDEX           ASSM                     65536          1048576
TABLE              ASSM                  94371840          1048576

ARROW:(MDINH@hawklas):PRIMARY> select sum(bytes)/1024/1024 from dba_segments where segment_name='ABC';

SUM(BYTES)/1024/1024
--------------------
                  96

ARROW:(MDINH@hawklas):PRIMARY> !df -h /oradata
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg01-LogVol01
                       59G   35G   22G  62% /

ARROW:(MDINH@hawklas):PRIMARY> alter system set events '1652 trace name errorstack off';

System altered.

ARROW:(MDINH@hawklas):PRIMARY> create index x on abc(txt) tablespace users;

Index created.

ARROW:(MDINH@hawklas):PRIMARY> @check_tbs.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 echo on
ARROW:(MDINH@hawklas):PRIMARY> col file_name for a55
ARROW:(MDINH@hawklas):PRIMARY> col segment_name for a30
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a30
ARROW:(MDINH@hawklas):PRIMARY> col segment_type for a18
ARROW:(MDINH@hawklas):PRIMARY> select extent_management, allocation_type, segment_space_management,
  2  initial_extent, next_extent, pct_increase
  3  from dba_tablespaces
  4  where tablespace_name=UPPER('&&tbs')
  5  ;

EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
---------- --------- ------ -------------- ----------- ------------
LOCAL      SYSTEM    AUTO            65536

ARROW:(MDINH@hawklas):PRIMARY> select tablespace_name, file_id, file_name, bytes/1024/1024 mb, autoextensible, maxbytes/1024/1024/1024 max_gb
  2  from dba_data_files
  3  where tablespace_name=UPPER('&&tbs')
  4  ;

TABLESPACE_NAME                   FILE_ID FILE_NAME                                                       MB AUT     MAX_GB
------------------------------ ---------- ------------------------------------------------------- ---------- --- ----------
USERS                                   4 /oradata/HAWKLAS/datafile/o1_mf_users_c7120bbr_.dbf            288 YES 31.9999847

ARROW:(MDINH@hawklas):PRIMARY> select max(bytes)/1024/1024 max_free_mb, sum(bytes)/1024/1024 total_free_mb, count(*)
  2  from dba_free_space
  3  where tablespace_name=UPPER('&&tbs')
  4  ;

MAX_FREE_MB TOTAL_FREE_MB   COUNT(*)
----------- ------------- ----------
         29            30          5

ARROW:(MDINH@hawklas):PRIMARY> select segment_type, segment_subtype, max(initial_extent), max(next_extent)
  2  from dba_segments
  3  where tablespace_name=UPPER('&&tbs')
  4  group by segment_type, segment_subtype
  5  ;

SEGMENT_TYPE       SEGMENT_SU MAX(INITIAL_EXTENT) MAX(NEXT_EXTENT)
------------------ ---------- ------------------- ----------------
INDEX              ASSM                     65536          1048576
LOBSEGMENT         ASSM                     65536          1048576
LOBINDEX           ASSM                     65536          1048576
TABLE              ASSM                  94371840          1048576

ARROW:(MDINH@hawklas):PRIMARY> select sum(bytes)/1024/1024 from dba_segments where segment_name='ABC';

SUM(BYTES)/1024/1024
--------------------
                  96

ARROW:(MDINH@hawklas):PRIMARY> select sum(bytes)/1024/1024 from dba_segments where segment_name='X';

SUM(BYTES)/1024/1024
--------------------
                 104

ARROW:(MDINH@hawklas):PRIMARY>
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/hawklas/hawklas/trace
$ tailf alert_hawklas.log
Thu Jan 07 13:34:50 2016
Starting background process CJQ0
Thu Jan 07 13:34:51 2016
CJQ0 started with pid=26, OS id=3088
Thu Jan 07 13:37:28 2016
OS Pid: 3097 executed alter system set events '1652 trace name errorstack off'
Thu Jan 07 13:37:59 2016
Starting background process SMCO
Thu Jan 07 13:37:59 2016
SMCO started with pid=24, OS id=3101
Thu Jan 07 13:41:03 2016
Thread 1 advanced to log sequence 78 (LGWR switch)
  Current log# 3 seq# 78 mem# 0: /oradata/HAWKLAS/onlinelog/o1_mf_3_c7129gc8_.log
  Current log# 3 seq# 78 mem# 1: /oradata/fra/HAWKLAS/onlinelog/o1_mf_3_c7129go4_.log
Thu Jan 07 13:41:10 2016
Archived Log entry 4040 added for thread 1 sequence 77 ID 0xbc2f7b69 dest 1:

Patching with OPLAN

Sat, 2015-12-19 12:47

From a time far, far way, I tweeted about Oracle Software Patching with OPLAN (Doc ID 1306814.1) and decided to give it a try.

First, you will need to configure X11 else error:
Can’t connect to X11 window server using ‘localhost:10.0’ as the value of the DISPLAY variable.

Second, you will need to using OPatch Version: 12.1.0.1.10, else error:
Caught exception: java.lang.ExceptionInInitializerError

If you like to see the results, then open and download Patch_Apply_Instructions_$PatchNumber.html from Google Drive

For some reason, opening does not work.

[grid@rac01:+ASM1:/home/grid]
$ /media/sf_Linux/patches/OPatch/oplan/oplan generateApplySteps /media/sf_Linux/patches/21744348/21523375/
from oplan /media/sf_Linux/patches/OPatch/oplan/../opatchauto-dir/opatchautocore/jlib/oracle.oplan.classpath.jar:/media/sf_Linux/patches/OPatch/oplan/../opatchauto-dir/opatchautocore/../opatchautodb/jlib/oplan_db.jar

Processing request...
Review the log messages captured in the following file: /u01/app/11.2.0.4/grid/cfgtoollogs/oplan/2015-12-19-10-32-11/log.txt
Success!

Follow the instructions outlined in the following Installation Instructions document and patch your system:

Apply Instructions (HTML) : /u01/app/11.2.0.4/grid/cfgtoollogs/oplan/2015-12-19-10-32-11/ApplyInstructions.html
Apply Instructions (TEXT) : /u01/app/11.2.0.4/grid/cfgtoollogs/oplan/2015-12-19-10-32-11/ApplyInstructions.txt

[grid@rac01:+ASM1:/u01/app/11.2.0.4/grid/cfgtoollogs/oplan]
$ cd /u01/app/11.2.0.4/grid/cfgtoollogs/oplan/2015-12-19-10-32-11/
[grid@rac01:+ASM1:/u01/app/11.2.0.4/grid/cfgtoollogs/oplan/2015-12-19-10-32-11]
$ ll
total 1284
-r--r-----. 1 grid oinstall 379154 Dec 19 10:32 ApplyInstructions.html
-r--r-----. 1 grid oinstall 8507 Dec 19 10:32 ApplyInstructions.txt
-r--r-----. 1 grid oinstall 9457 Dec 19 10:32 configuration.png
-r--r-----. 1 grid oinstall 42733 Dec 19 10:32 InplaceApplyNonRollingManual.txt
-r--r-----. 1 grid oinstall 36741 Dec 19 10:32 InplaceApplyRollingAuto.txt
-r--r-----. 1 grid oinstall 44548 Dec 19 10:32 InplaceApplyRollingManual.txt
-r--r-----. 1 grid oinstall 613286 Dec 19 10:32 log.txt
-r--r-----. 1 grid oinstall 0 Dec 19 10:32 log.txt.lck
dr-xr-x---. 3 grid oinstall 4096 Dec 19 10:32 machine-readable
-r--r-----. 1 grid oinstall 69478 Dec 19 10:32 OplaceApplyRolling.txt
-r--r-----. 1 grid oinstall 26608 Dec 19 10:32 OplaceSwitchbackRolling.txt
-r--r-----. 1 grid oinstall 353 Dec 19 10:32 README
-r--r-----. 1 grid oinstall 60991 Dec 19 10:32 README.html
[grid@rac01:+ASM1:/u01/app/11.2.0.4/grid/cfgtoollogs/oplan/2015-12-19-10-32-11]
$ 

Observation: OPLAN may need to be created after each patch is applied.

Combo of OJVM Component 11.2.0.4.5 DB PSU + GI PSU 11.2.0.4.8 (Oct2015) Patch contain the following patches:
Patch 21523375 – Database Grid Infrastructure Patch Set Update 11.2.0.4.8 (Oct2015) –> RAC-Rolling Installable
Patch 21555791 – Oracle JavaVM Component 11.2.0.4.5 Database PSU (OCT2015) –> Non RAC-Rolling Installable
Patch 19852360 – Oracle JavaVM Component 11.2.0.4.1 Database PSU – Generic JDBC Patch (OCT2014) –> RAC-Rolling Installable

Patch 19852360 is included as part of Patch 21555791 for the DATABASE.
Patch 19852360 instructions to apply patch for GRID is not available from OPLAN but is available from README.


Pet Peeve crs start/stop

Sat, 2015-12-19 08:22

When stopping crs, there are 50+ outputs and is Attempting really necessary?

Conversely, when starting crs, there are only 1 output and we all know the process has not completed since crsctl stat fails.

Wouldn’t it be nice if crsctl start provides some useful information as well and prompt the the all the processes are started?

What am I missing?

[root@rac01:/root]
# crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac01'
CRS-2673: Attempting to stop 'ora.crsd' on 'rac01'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac01'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'rac01'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'rac01'
CRS-2673: Attempting to stop 'ora.emu.db' on 'rac01'
CRS-2673: Attempting to stop 'ora.dg_acfs.vg_acfs.acfs' on 'rac01'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac01'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'rac01' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'rac01'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac01' succeeded
CRS-2673: Attempting to stop 'ora.rac01.vip' on 'rac01'
CRS-2677: Stop of 'ora.dg_acfs.vg_acfs.acfs' on 'rac01' succeeded
CRS-2673: Attempting to stop 'ora.DG_ACFS.dg' on 'rac01'
CRS-2677: Stop of 'ora.registry.acfs' on 'rac01' succeeded
CRS-2677: Stop of 'ora.emu.db' on 'rac01' succeeded
CRS-2673: Attempting to stop 'ora.DATA2.dg' on 'rac01'
CRS-2677: Stop of 'ora.scan1.vip' on 'rac01' succeeded
CRS-2672: Attempting to start 'ora.scan1.vip' on 'rac02'
CRS-2677: Stop of 'ora.rac01.vip' on 'rac01' succeeded
CRS-2672: Attempting to start 'ora.rac01.vip' on 'rac02'
CRS-2677: Stop of 'ora.DG_ACFS.dg' on 'rac01' succeeded
CRS-2676: Start of 'ora.scan1.vip' on 'rac02' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'rac02'
CRS-2676: Start of 'ora.rac01.vip' on 'rac02' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'rac02' succeeded
CRS-2677: Stop of 'ora.DATA2.dg' on 'rac01' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac01'
CRS-2677: Stop of 'ora.asm' on 'rac01' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'rac01'
CRS-2677: Stop of 'ora.ons' on 'rac01' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac01'
CRS-2677: Stop of 'ora.net1.network' on 'rac01' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac01' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac01' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac01'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac01'
CRS-2673: Attempting to stop 'ora.asm' on 'rac01'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac01'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'rac01'
CRS-2677: Stop of 'ora.ctssd' on 'rac01' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac01' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'rac01' succeeded
CRS-2677: Stop of 'ora.drivers.acfs' on 'rac01' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac01' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac01'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac01' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac01'
CRS-2677: Stop of 'ora.cssd' on 'rac01' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'rac01'
CRS-2677: Stop of 'ora.crf' on 'rac01' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac01'
CRS-2677: Stop of 'ora.gipcd' on 'rac01' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac01'
CRS-2677: Stop of 'ora.gpnpd' on 'rac01' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac01' has completed
CRS-4133: Oracle High Availability Services has been stopped.

[root@rac01:/root]
# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

[root@rac01:/root]
# crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager

[root@rac01:/root]
# crsctl stat res -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.
[root@rac01:/root]
#

TFA installed as part of 21523375 (Oct2015 CPU)

Fri, 2015-12-18 13:01

+++ Patch 21523375 – Oracle Grid Infrastructure Patch Set Update 11.2.0.4.8 (Oct2015) (Includes Database PSU 11.2.0.4.8)
+++ Case 1: GI Home and the Database Homes that are not shared and ACFS file system is not configured.

After the completion of Patch 21523375, TFA was installed.

If you need to shutdown processes running from grid, TFA will need to be stopped as well (# /etc/init.d/init.tfa stop) since crsctl stop crs does not stop TFA.

I ***incorrectly*** tweeted that start crs starts TFA.

TFA was started from patch apply.

Interested to see what happens in the next patching cycle.

[root@rac02:/root]
# $ORACLE_HOME/OPatch/opatch auto /u01/app/grid/patches/21744348/21523375 -ocmrf /tmp/ocm.rsp
Executing /u01/app/11.2.0.4/grid/perl/bin/perl /u01/app/11.2.0.4/grid/OPatch/crs/patch11203.pl -patchdir /u01/app/grid/patches/21744348 -patchn 21523375 -ocmrf /tmp/ocm.rsp -paramfile /u01/app/11.2.0.4/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/11.2.0.4/grid/cfgtoollogs/opatchauto2015-12-18_08-18-46.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/11.2.0.4/grid/cfgtoollogs/opatchauto2015-12-18_08-18-46.report.log

2015-12-18 08:18:46: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/app/11.2.0.4/grid/crs/install/crsconfig_params

Stopping RAC /u01/app/oracle/product/11.2.0.4/db_1 ...
Stopped RAC /u01/app/oracle/product/11.2.0.4/db_1 successfully

patch /u01/app/grid/patches/21744348/21523375/21352635  apply successful for home  /u01/app/oracle/product/11.2.0.4/db_1
patch /u01/app/grid/patches/21744348/21523375/21352649/custom/server/21352649  apply successful for home  /u01/app/oracle/product/11.2.0.4/db_1

Stopping CRS...
Stopped CRS successfully

patch /u01/app/grid/patches/21744348/21523375/21352635  apply successful for home  /u01/app/11.2.0.4/grid
patch /u01/app/grid/patches/21744348/21523375/21352649  apply successful for home  /u01/app/11.2.0.4/grid
patch /u01/app/grid/patches/21744348/21523375/21352642  apply successful for home  /u01/app/11.2.0.4/grid

Starting CRS...
Installing Trace File Analyzer
CRS-4123: Oracle High Availability Services has been started.

Starting RAC /u01/app/oracle/product/11.2.0.4/db_1 ...
Started RAC /u01/app/oracle/product/11.2.0.4/db_1 successfully

opatch auto succeeded.
[root@rac02:/root]
#

[root@rac02:/root]
# ps -ef|grep tfa
root     11017     1  0 08:28 ?        00:00:00 /bin/sh /etc/init.d/init.tfa run
root     12980 12756  0 10:35 pts/0    00:00:00 grep tfa
root     26988     1  1 08:31 ?        00:01:33 /u01/app/11.2.0.4/grid/jdk/jre/bin/java -Xms128m -Xmx512m -classpath /u01/app/11.2.0.4/grid/tfa/rac02/tfa_home/jlib/RATFA.jar:/u01/app/11.2.0.4/grid/tfa/rac02/tfa_home/jlib/je-5.0.84.jar:/u01/app/11.2.0.4/grid/tfa/rac02/tfa_home/jlib/ojdbc5.jar:/u01/app/11.2.0.4/grid/tfa/rac02/tfa_home/jlib/commons-io-2.1.jar oracle.rat.tfa.TFAMain /u01/app/11.2.0.4/grid/tfa/rac02/tfa_home

[root@rac02:/root]
# crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac02'
CRS-2673: Attempting to stop 'ora.crsd' on 'rac02'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac02'
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac02' has completed
CRS-4133: Oracle High Availability Services has been stopped.

[root@rac02:/root]
# ps -ef|egrep 'oracle|grid|agent'
gdm       2968  2926  0 06:11 ?        00:00:00 /usr/libexec/polkit-gnome-authentication-agent-1
root     14811 12756  0 10:39 pts/0    00:00:00 egrep oracle|grid|agent
root     26988     1  1 08:31 ?        00:01:36 /u01/app/11.2.0.4/grid/jdk/jre/bin/java -Xms128m -Xmx512m -classpath /u01/app/11.2.0.4/grid/tfa/rac02/tfa_home/jlib/RATFA.jar:/u01/app/11.2.0.4/grid/tfa/rac02/tfa_home/jlib/je-5.0.84.jar:/u01/app/11.2.0.4/grid/tfa/rac02/tfa_home/jlib/ojdbc5.jar:/u01/app/11.2.0.4/grid/tfa/rac02/tfa_home/jlib/commons-io-2.1.jar oracle.rat.tfa.TFAMain /u01/app/11.2.0.4/grid/tfa/rac02/tfa_home

TFA needs to be stopped manually

[root@rac02:/root]
# /etc/init.d/init.tfa stop
Stopping TFA
TFA-00002 : Oracle Trace File Analyzer (TFA) is not running
TFAmain Force Stopped Successfully
Killing TFA running with pid 26988
. . .
Successfully stopped TFA..
[root@rac02:/root]
# ps -ef|egrep 'oracle|grid|agent'
gdm       2968  2926  0 06:11 ?        00:00:00 /usr/libexec/polkit-gnome-authentication-agent-1
root     15141 12756  0 10:40 pts/0    00:00:00 egrep oracle|grid|agent
[root@rac02:/root]
# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

[root@rac02:/root]
# ps -ef|grep -i tfa
root     11017     1  0 08:28 ?        00:00:00 /bin/sh /etc/init.d/init.tfa run
root     16558 12756  0 10:42 pts/0    00:00:00 grep -i tfa

TFA needs to be started manually

[root@rac02:/root]
# /etc/init.d/init.tfa start
Starting TFA..
start: Job is already running: oracle-tfa
Waiting up to 100 seconds for TFA to be started..
. . . . .
Successfully started TFA Process..
. . . . .
TFA Started and listening for commands
[root@rac02:/root]
# ps -ef|grep -i tfa
root     11017     1  0 08:28 ?        00:00:00 /bin/sh /etc/init.d/init.tfa run
root     16739     1 99 10:43 ?        00:00:13 /u01/app/11.2.0.4/grid/jdk/jre/bin/java -Xms128m -Xmx512m -classpath /u01/app/11.2.0.4/grid/tfa/rac02/tfa_home/jlib/RATFA.jar:/u01/app/11.2.0.4/grid/tfa/rac02/tfa_home/jlib/je-5.0.84.jar:/u01/app/11.2.0.4/grid/tfa/rac02/tfa_home/jlib/ojdbc5.jar:/u01/app/11.2.0.4/grid/tfa/rac02/tfa_home/jlib/commons-io-2.1.jar oracle.rat.tfa.TFAMain /u01/app/11.2.0.4/grid/tfa/rac02/tfa_home
root     16938 12756  0 10:43 pts/0    00:00:00 grep -i tfa
[root@rac02:/root]

TFA does not have a status and needs to be checked using ps -ef

[root@rac02:/root]
# /etc/init.d/init.tfa
Usage: /etc/init.d/init.tfa {stop|start|shutdown|restart}
[root@rac02:/root]
#

UPDATE: Dec 19 2015

[grid@rac01:+ASM1:/home/grid]
$ echo $ORACLE_BASE
/u01/app/grid
[grid@rac01:+ASM1:/home/grid]
$ cd $ORACLE_BASE

TFA directory beneath grid $ORACLE_BASE

[grid@rac01:+ASM1:/u01/app/grid]
$ ll
total 36
drwxrwxr-x. 4 grid   oinstall 4096 Nov 30  2014 cfgtoollogs
drwxrwxr-x. 2 grid   oinstall 4096 Nov 30  2014 checkpoints
drwxrwxr-x. 2 grid   oinstall 4096 Nov 30  2014 Clusterware
drwxrwxr-x. 4 grid   oinstall 4096 Nov 30  2014 diag
drwxrwxr-x. 3 oracle oinstall 4096 Dec  4  2014 oradiag_oracle
drwxrwxr-x. 3 root   root     4096 Dec  1  2014 oradiag_root
drwxrwxr-x. 3 grid   oinstall 4096 Nov 30  2014 rac01
drwxr-x--x. 4 root   root     4096 Nov 30  2014 tfa
drwxrwxr-x. 9 grid   oinstall 4096 Dec 18  2014 xag
[grid@rac01:+ASM1:/u01/app/grid]
$ cd tfa/
[grid@rac01:+ASM1:/u01/app/grid/tfa]
$ ll
ls: cannot open directory .: Permission denied
[grid@rac01:+ASM1:/u01/app/grid/tfa]

Windows Task Scheduler CMD

Wed, 2015-11-18 18:56

Don’t get me wrong. GUI can be terrific but it does have its place.

When the connection between you and the the target host are multiple servers across the continent, the latency will drive me crazy mad.

Hence, my exploration to learn more command line.

Enough of my rant, let’s demo.

Tested on Windows 7
C:\Users\dinh>ver

Microsoft Windows [Version 6.1.7601]

C:\Users\dinh>
List by Folders:

C:\Users\dinh>schtasks /query /fo table /nh

Folder: \
DropboxUpdateTaskUserS-1-5-21-992222644- 18-Nov-2015 11:07:00 P Ready
DropboxUpdateTaskUserS-1-5-21-992222644- 18-Nov-2015 8:07:00 PM Ready
G2MUpdateTask-S-1-5-21-992222644-2553030 18-Nov-2015 7:45:00 PM Ready
G2MUploadTask-S-1-5-21-992222644-2553030 18-Nov-2015 9:31:00 PM Ready
GoogleUpdateTaskUserS-1-5-21-992222644-2 19-Nov-2015 6:55:00 PM Ready
GoogleUpdateTaskUserS-1-5-21-992222644-2 18-Nov-2015 7:55:00 PM Ready
hpUtility.exe_{3FB4CD0B-D6EE-4B93-BD0E-C N/A                    Ready
Microsoft_Hardware_Launch_ipoint_exe     N/A                    Ready
Microsoft_Hardware_Launch_itype_exe      N/A                    Ready
Microsoft_Hardware_Launch_mousekeyboardc N/A                    Ready

Folder: \Microsoft
INFO: There are no scheduled tasks presently available at your access level.

Folder: \Microsoft\Windows
INFO: There are no scheduled tasks presently available at your access level.

Folder: \Microsoft\Windows\Active Directory Rights Management Services Client
AD RMS Rights Policy Template Management Disabled
AD RMS Rights Policy Template Management N/A                    Ready

C:\Users\dinh>
List by TaskName:

C:\Users\dinh>schtasks /query /fo list

Folder: \
HostName:      CMWPHV1
TaskName:      \DropboxUpdateTaskUserS-1-5-21-992222644-2553030573-2413354843-1005Core
Next Run Time: 18-Nov-2015 11:07:00 PM
Status:        Ready
Logon Mode:    Interactive only

HostName:      CMWPHV1
TaskName:      \DropboxUpdateTaskUserS-1-5-21-992222644-2553030573-2413354843-1005UA
Next Run Time: 18-Nov-2015 8:07:00 PM
Status:        Ready
Logon Mode:    Interactive only

HostName:      CMWPHV1
TaskName:      \G2MUpdateTask-S-1-5-21-992222644-2553030573-2413354843-1005
Next Run Time: 18-Nov-2015 7:45:00 PM
Status:        Ready
Logon Mode:    Interactive only

HostName:      CMWPHV1
TaskName:      \G2MUploadTask-S-1-5-21-992222644-2553030573-2413354843-1005
Next Run Time: 18-Nov-2015 9:31:00 PM
Status:        Ready
Logon Mode:    Interactive only

HostName:      CMWPHV1
TaskName:      \GoogleUpdateTaskUserS-1-5-21-992222644-2553030573-2413354843-1005Core
Next Run Time: 19-Nov-2015 6:55:00 PM
Status:        Ready
Logon Mode:    Interactive only

HostName:      CMWPHV1
TaskName:      \GoogleUpdateTaskUserS-1-5-21-992222644-2553030573-2413354843-1005UA
Next Run Time: 18-Nov-2015 7:55:00 PM
Status:        Ready
Logon Mode:    Interactive only

HostName:      CMWPHV1
TaskName:      \hpUtility.exe_{3FB4CD0B-D6EE-4B93-BD0E-C69652A66A6C}
Next Run Time: N/A
Status:        Ready
Logon Mode:    Interactive only

HostName:      CMWPHV1
TaskName:      \Microsoft_Hardware_Launch_ipoint_exe
Next Run Time: N/A
Status:        Ready
Logon Mode:    Interactive/Background

HostName:      CMWPHV1
TaskName:      \Microsoft_Hardware_Launch_itype_exe
Next Run Time: N/A
Status:        Ready
Logon Mode:    Interactive/Background

HostName:      CMWPHV1
TaskName:      \Microsoft_Hardware_Launch_mousekeyboardcenter_exe
Next Run Time: N/A
Status:        Ready
Logon Mode:    Interactive/Background

Folder: \Microsoft
INFO: There are no scheduled tasks presently available at your access level.

Folder: \Microsoft\Windows
INFO: There are no scheduled tasks presently available at your access level.

Folder: \Microsoft\Windows\Active Directory Rights Management Services Client
HostName:      CMWPHV1
TaskName:      \Microsoft\Windows\Active Directory Rights Management Services Client\AD RMS Rights Policy Template Management (Autom
ated)
Next Run Time: Disabled
Status:
Logon Mode:    Interactive/Background

HostName:      CMWPHV1
TaskName:      \Microsoft\Windows\Active Directory Rights Management Services Client\AD RMS Rights Policy Template Management (Autom
ated)
Next Run Time: Disabled
Status:
Logon Mode:    Interactive/Background

HostName:      CMWPHV1
TaskName:      \Microsoft\Windows\Active Directory Rights Management Services Client\AD RMS Rights Policy Template Management (Manua
l)
Next Run Time: N/A
Status:        Ready
Logon Mode:    Interactive/Background

C:\Users\dinh>
Search for TaskName:

C:\Users\dinh>schtasks /query /fo LIST |findstr /I dropbox

TaskName:      \DropboxUpdateTaskUserS-1-5-21-992222644-2553030573-2413354843-1005Core
TaskName:      \DropboxUpdateTaskUserS-1-5-21-992222644-2553030573-2413354843-1005UA
List TaskName Summary:

C:\Users\dinh>schtasks /query /fo LIST /tn “\DropboxUpdateTaskUserS-1-5-21-992222644-2553030573-2413354843-1005UA”

Folder: \
HostName:      CMWPHV1
TaskName:      \DropboxUpdateTaskUserS-1-5-21-992222644-2553030573-2413354843-1005UA
Next Run Time: 18-Nov-2015 8:07:00 PM
Status:        Ready
Logon Mode:    Interactive only
List TaskName Detail:

C:\Users\dinh>schtasks /query /fo LIST /v /tn “\DropboxUpdateTaskUserS-1-5-21-992222644-2553030573-2413354843-1005UA”

Folder: \
HostName:                             CMWPHV1
TaskName:                             \DropboxUpdateTaskUserS-1-5-21-992222644-2553030573-2413354843-1005UA
Next Run Time:                        18-Nov-2015 8:07:00 PM
Status:                               Ready
Logon Mode:                           Interactive only
Last Run Time:                        18-Nov-2015 7:07:00 PM
Last Result:                          0
Author:                               dinh
Task To Run:                          C:\Users\dinh\AppData\Local\Dropbox\Update\DropboxUpdate.exe /ua /installsource scheduler
Start In:                             N/A
Comment:                              Keeps your Dropbox software up to date. If this task is disabled or stopped, your Dropbox soft
ware will not be kept up to date, meaning security vulnerabilities that may arise cannot be fixed and features may not work. This ta
sk uninstalls itself when t
Scheduled Task State:                 Enabled
Idle Time:                            Disabled
Power Management:
Run As User:                          dinh
Delete Task If Not Rescheduled:       Enabled
Stop Task If Runs X Hours and X Mins: Disabled
Schedule:                             Scheduling data is not available in this format.
Schedule Type:                        Daily
Start Time:                           11:07:00 PM
Start Date:                           17-Jul-2015
End Date:                             N/A
Days:                                 Every 1 day(s)
Months:                               N/A
Repeat: Every:                        1 Hour(s), 0 Minute(s)
Repeat: Until: Time:                  None
Repeat: Until: Duration:              24 Hour(s), 0 Minute(s)
Repeat: Stop If Still Running:        Disabled

C:\Users\dinh>

If you really want to use GUI:

Launch GUI Task Scheduler:

C:\Users\dinh>control schedtasks

Reference: Schtasks


Extent Allocation

Wed, 2015-11-18 17:43

Some references on extents dealing with extent trimming, initial extent for parallel load, tablespace fragmentation, etc…

ASM AU Size And LMT AUTOALLOCATE
http://oracle-randolf.blogspot.com/2013/04/asm-au-size-and-lmt-autoallocate.html

Parallel Load: Uniform or AutoAllocate extents?
https://blogs.oracle.com/datawarehousing/entry/parallel_load_uniform_or_autoallocate

Tom Kyte covers this problem in great details in his post Loading and Extents
http://www.oracle.com/technetwork/issue-archive/2007/07-may/o37asktom-101781.html

System Managed Extent Size – 11g Improvements
https://antognini.ch/2009/08/system-managed-extent-size-11g-improvements/

PX and system allocation
https://jonathanlewis.wordpress.com/2012/06/14/px-and-system-allocation/

Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later (Doc ID 1295484.1)

Bug 19912552 : PARTITIONED INDEXES CREATED WITH 8M INITIAL EXTENT WHEN _INDEX_PARTITION_LARGE

Segments of type INDEX PARTITION or INDEX SUBPARTITION will be created with 8M initial extent in version 12.1.0.2, instead of 64K in earlier versions.
Global indexes or indexes on non partitioned tables still have 64K.


Using emctl to Create Blackout

Sat, 2015-10-24 13:16

When it comes to using EM12c, I do not like using GUI, especially in the environment I am working with.

Aside from the rant, it’s more difficult to document steps using GUI and to reproduce or automate the steps.

If you are interested in using command line, then read on.

The blackout was created at the target (monitored) server.

Determine location of Agent installation.

[oracle@arrow ~]$ ps -ef|grep agent_inst

oracle    36878  36125  0 12:56 pts/1    00:00:00 grep agent_inst
oracle   108498      1  0 Oct17 ?        00:00:22 /u01/app/oracle/product/12.1/cc_agent/core/12.1.0.3.0/perl/bin/perl 
/u01/app/oracle/product/12.1/cc_agent/core/12.1.0.3.0/bin/emwd.pl 
agent /u01/app/oracle/product/12.1/cc_agent/agent_inst/sysman/log/emagent.nohup

[oracle@arrow ~]$ cd /u01/app/oracle/product/12.1/cc_agent/agent_inst/bin/
Create blackout.

[oracle@arrow bin]$ ./emctl start blackout WaitUntilMonday -nodeLevel -d 3:00

Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Blackout WaitUntilMonday added successfully
EMD reload completed successfully
Verify blackout status.

[oracle@arrow bin]$ ./emctl status blackout

Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Blackoutname = WaitUntilMonday
Targets = (arrow.local:host,)
Time = ({2015-10-23|12:57:28|180 Min,|} )
Expired = False

OOPS! Forgot to RTFM!

The blackout was created for 3 hours and not 3 days which was the intention.

The -d option is used to specify the duration of the blackout, e.g. -d 5 02:30 (5 days 2 hours and 30 minutes)

https://docs.oracle.com/cd/E24628_01/doc.121/e24473/blackouts.htm#EMADM13166

Stop blackout

[oracle@arrow bin]$ ./emctl stop blackout WaitUntilMonday

Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Blackout WaitUntilMonday stopped successfully
EMD reload completed successfully

[oracle@arrow bin]$ ./emctl status blackout
Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
No Blackout registered.
Reference:

http://cloudcontrol12c.blogspot.com/2013/07/create-node-level-blackouts-on.html


Using emctl to Create Blackout

Sat, 2015-10-24 13:16

When it comes to using EM12c, I do not like using GUI, especially in the environment I am working with.

Aside from the rant, it’s more difficult to document steps using GUI and to reproduce or automate the steps.

If you are interested in using command line, then read on.

The blackout was created at the target (monitored) server.

Determine location of Agent installation.

[oracle@arrow ~]$ ps -ef|grep agent_inst

oracle    36878  36125  0 12:56 pts/1    00:00:00 grep agent_inst
oracle   108498      1  0 Oct17 ?        00:00:22 /u01/app/oracle/product/12.1/cc_agent/core/12.1.0.3.0/perl/bin/perl 
/u01/app/oracle/product/12.1/cc_agent/core/12.1.0.3.0/bin/emwd.pl 
agent /u01/app/oracle/product/12.1/cc_agent/agent_inst/sysman/log/emagent.nohup

[oracle@arrow ~]$ cd /u01/app/oracle/product/12.1/cc_agent/agent_inst/bin/
Create blackout.

[oracle@arrow bin]$ ./emctl start blackout WaitUntilMonday -nodeLevel -d 3:00

Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Blackout WaitUntilMonday added successfully
EMD reload completed successfully
Verify blackout status.

[oracle@arrow bin]$ ./emctl status blackout

Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Blackoutname = WaitUntilMonday
Targets = (arrow.local:host,)
Time = ({2015-10-23|12:57:28|180 Min,|} )
Expired = False

OOPS! Forgot to RTFM!

The blackout was created for 3 hours and not 3 days which was the intention.

The -d option is used to specify the duration of the blackout, e.g. -d 5 02:30 (5 days 2 hours and 30 minutes)

https://docs.oracle.com/cd/E24628_01/doc.121/e24473/blackouts.htm#EMADM13166

Stop blackout

[oracle@arrow bin]$ ./emctl stop blackout WaitUntilMonday

Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Blackout WaitUntilMonday stopped successfully
EMD reload completed successfully

[oracle@arrow bin]$ ./emctl status blackout
Oracle Enterprise Manager Cloud Control 12c Release 3  
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
No Blackout registered.
Reference:

http://cloudcontrol12c.blogspot.com/2013/07/create-node-level-blackouts-on.html