ORA-16826: apply service state is inconsistent with the DelayMins property

When checking the configuration status in DGMGRL receiving the below error.

DGMGRL> show configuration

Configuration - DG_PROD

  Protection Mode: MaxPerformance
  Databases:
    Prod- Primary database
    Stdby - Physical standby database
      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL> show database stdby

Database - Stdby 

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   (unknown)

  Apply Lag:       21 hours 51 minutes 5 seconds (computed 40 seconds ago)

  Apply Rate:      (unknown)

  Real Time Query: OFF

  Instance(s):

    ENTP1

  Database Warning(s):

    ORA-16826: apply service state is inconsistent with the DelayMins property

Database Status:

WARNING

As per the metalink document 1608166.1 we need to follow below to resolve the issue.

Solution:

*******

You need to restart the MRP with real-time apply mode.

DGMGRL> edit database stdby set state=apply-off;

DGMGRL> edit database stdby set state=apply-on;

or

SQL>alter database recover managed standby database cancel;

SQL> alter database recover managed standby database using current logfile disconnect FROM SESSION PARALLEL 10;

Here we need to start the MRP with parallel as there is a gap between standby and primary. And make sure Standby redo logs files are created on standby.

But we followed the below simple approach to resolve the issue. Just disable and again enable the configuration that will start the MRP with parallel.

DGMGRL> show configuration

Configuration - DG_PROD

  Protection Mode: MaxPerformance

  Databases:

    Prod- Primary database

    Stdby - Physical standby database

      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED

Configuration Status:

WARNING

 

DGMGRL> disable configuration;

Disabled.

DGMGRL> show configuration;

Configuration - DG_PROD

  Protection Mode: MaxPerformance

  Databases:

    Prod - Primary database

    Stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED 

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration;

Configuration - DG_PROD

  Protection Mode: MaxPerformance

  Databases:

    Prod - Primary database

    Stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

Warning: ORA-16610: command ‘Broker automatic health check’ in progress

DGMGRL> show configuration;

Configuration - DG_PROD

  Protection Mode: MaxPerformance

  Databases:

    Prod - Primary database

    Stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

Alert log:

***********

Only allocated 111 recovery slaves (requested 128)

Parallel Media Recovery started with 111 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Mon Jun 13 21:11:07 2016

Media Recovery Log /oraEMC/prod/arch1/Prod_1_89006_51323279.ARC

Mon Jun 13 21:11:07 2016

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE 

Happy Learning :)

How to delete the flashback logs in Oracle

SQL> select name,open_mode,flashback_on from v$database;

NAME      OPEN_MODE            FLASHBACK_ON
--------- -------------------- ------------------
Prod   READ WRITE           YES


SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                                    0                         0               0          0
REDO LOG                                            0                         0               0          0
ARCHIVED LOG                                   0                         0               0          0
BACKUP PIECE                                     0                         0               0          0
IMAGE COPY                                         0                         0               0          0
FLASHBACK LOG                          49.53                     39.52           27         0
FOREIGN ARCHIVED LOG                 0                         0               0          0
AUXILIARY DATAFILE COPY            0                         0               0          0

8 rows selected.

SQL> col name for a20
select name, (space_limit/1024/1024/1024) ||'GB' as Space_Limit, (space_used/1024/1024/1024)||'GB' as Space_Used from v$recovery_file_dest;
SQL>
NAME                 SPACE_LIMIT                                SPACE_USED
-------------------- ------------------------------------------ ------------------------------------------
+FEDEX_FLASH         100GB                                      49.52GB

SQL> select * from v$restore_point;

no rows selected

SQL> select sum(FLASHBACK_SIZE)/1024/1024/1024 from V$FLASHBACK_DATABASE_LOG;

SUM(FLASHBACK_SIZE)/1024/1024/1024
----------------------------------
                              49.5

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FLASH/prod/
Oldest online log sequence     825
Next log sequence to archive   832
Current log sequence           832

Reduce the space for FRA so it will delete the flashback logs automatically the same you can observe in alertlog.

SQL>  alter system set db_recovery_file_dest_size=19g scope=both sid='*';

System altered.

Alertlog Details:
*****************
Sat Apr 30 06:04:29 2016
ALTER SYSTEM SET db_recovery_file_dest_size=19G SCOPE=BOTH SID='*';
Sat Apr 30 06:04:29 2016
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_1.3351.896398699
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_4.8287.896533879
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_2.9436.896398703
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_5.7796.897271709
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_6.7771.897273413
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_7.7810.897275073
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_8.7823.897283099
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_9.4000.897289741
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_12.9334.910404199
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_13.9355.910405725


SQL> select sum(FLASHBACK_SIZE)/1024/1024/1024 from V$FLASHBACK_DATABASE_LOG;

SUM(FLASHBACK_SIZE)/1024/1024/1024
----------------------------------
                              17.5

Happy Learning :)

RMAN-08591: WARNING: invalid archived log deletion policy

Receiving the below error in backup log

Error Details:
--------------
RMAN-08591: WARNING: invalid archived log deletion policy

Cause:
------
If archive log location is set to FRA, then there might be a chance of the deleting the archives automatically when the space pressure in FRA.
In that case at least one of the destination standby must be set as a "MANDATORY" destination.

Solution:
----------

To eliminate the RMAN warning message, at least one archive destination must be set as a mandatory destination.

1. Log in to the broker command line utility

[oracle@prd-srv01 log]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

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

Configuration - prod_dg

  Protection Mode: MaxPerformance
  Members:
  prod - Primary database
    stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 7 seconds ago)

2. For the standby site check the "Binding" property

DGMGRL> show database verbose stdby

Database - stdby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      11 minutes (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 518.00 KByte/s
  Active Apply Rate:  457.00 KByte/s
  Maximum Apply Rate: 17.62 MByte/s
  Real Time Query:    ON
  Instance(s):
    stdby

  Properties:

----------
----------
 Binding        = 'optional'
----------
----------

3. Set the "Binding" property to MANDATORY

DGMGRL> edit database stdby set property Binding='mandatory';
Property "binding" updated

DGMGRL> exit

Once the broker configuration has set a standby site as a mandatory destination, then RMAN will not report this error again.

Reference:
----------
Data Guard Physical Standby - RMAN configure archivelog deletion policy reports RMAN-08591 (Doc ID 1984064.1)

Happy Learning :)

PRVF-5300 : Failed to retrieve active version for CRS on this node

When i am trying to install the non-RAC database installation fails with the below error

PRVF-5300 : Failed to retrieve active version for CRS on this node

MOS DOC: Installation of Standalone (Non-RAC) 11gR2 Database Fails with Errors: "PRVF-5300, PRKC-1033, PRCT-1406" (Doc ID 1380126.1)

The issue is we have the RAC setup in past on this server and we have removed few days back. Now we are trying to install the Oracle Restart (ASM+DB) but installation failing.

Here we have identified the problem, as OCR/OLR is still present in the server due to that installation failing.  Once you removed the OCR/OLR that solves the our problem.

Check the OCR/OLR locations
[oracle@test dba]$ ls -lhrt /etc/ocr.loc
ls: /etc/ocr.loc: No such file or directory
[oracle@test dba]$ ls -lhrt /etc/oracle/olr.loc
-rw-r----- 1 root oinstall 103 Jul 22  2015 /etc/oracle/olr.loc

so, here olr is present in server level. Remove the olr disk and start the installation


Happy Learning :)

How to resize the datafile in Primary

Resize the datafile help us when there is not much space is available in the OS level.

1.Check the datafiles under the tablespace.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='PROD_DATA';

FILE_NAME                                        TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

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

+DATA/PROD/DATAFILE/prod_data2.256.894385677   PROD_DATA                                365 YES        374

Here only 9gb free space left. 

2. Check is there any provisioned disks are there to add. 

SQL> col path for a35

Select path,disk_number,name,mount_status,header_status,os_mb/1024,total_mb/1024,free_mb/1024,FAILGROUP from v$asm_disk where header_status<>'MEMBER' ;

no rows selected

3. Now identify the HWM for the datafile for how much space to we can resize. The below script will prompt for the datafile.

set linesize 400

col tablespace_name format a20

col file_size format 9999999

col file_name format a66

col hwm format 9999999

col can_save format 99999

SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,

ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm 

FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,

(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs WHERE ddf.file_id = ebf.file_id 

AND de.file_id = ebf.file_id AND ddf.file_name='&Datafile_Name' and de.block_id = ebf.maximum ORDER BY 1,2); 

 Enter value for Datafile: +DATA/PROD/DATAFILE/prod_data2.256.894385677 

TABLESPACE_NAME      FILE_NAME                                                           FILE_SIZE        HWM CAN_SAVE

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

PROD_DATA     +DATA/PROD/DATAFILE/prod_data2.256.894385677         373760     361254    12506

Based on the above output we came to know that we can save upto 12506mb. 

4. Resize the datafile

SQL> alter database datafile '+DATA/PROD/DATAFILE/prod_data2.256.894385677' resize 361255m;

Database altered.

Now check the utilization.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='PROD_DATA';

FILE_NAME                                        TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

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

+DATA/PROD/DATAFILE/prod_data2.256.894385677    PROD_DATA                                352.78   YES        374

If it has standby, the above change will reflect on standby as well. No need to take any action on standby.

Happy Learning :)

EXPDP/IMPDP fails with ORA-31693, ORA-29913, ORA-31640, ORA-19505, ORA-27037 in RAC

While doing import using impdp i am encountering the below issue.

Error Details:
*************
ORA-31693: Table data object "sivaram"."TITAN":"SYS_P20689" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-31640: unable to open dump file "/u01/refresh/expdp_TABLES_First_Set_03.dmp" for read
ORA-19505: failed to identify file "/u01/refresh/expdp_TABLES_First_Set_03.dmp"
ORA-27037: unable to obtain file status

But i have checked the file status in  server level it is present. But impmort unble to identify the file status. Also checked the permissions on directory, it looks fine.

[oracle@prod02 refresh]$ ls -lhrt /u01/refresh/expdp_TABLES_First_Set_03.dmp
-rw-r----- 1 oracle dba 5.6G Apr 15 20:42 /u01/refresh/expdp_TABLES_First_Set_03.dmp

Parfile for Import:
*******************
$ more impdp_TABLES.par
directory=refresh
dumpfile=expdp_TABLES_First_Set_%U.dmp
logfile=impdp_TABLES.log
parallel=6
tables=sivaram.TITAN

The problem is, impdp process is looking for the dump file on the other node in the cluster, eventhough i started from the local node only due to i mentioned parallel in the par file. Prior to 11.2, once you establish the connection to the database, an instance selected and the master process and all the worker process run on that instance only.
From 11.2, CLUSTER parameter introduced

CLUSTER=Y|N (Default: Y)
Purpose: Whether Datapump can use RAC resources and start workers on other instances in cluster.

Solution:
*********
1. Add the CLUSTER=N in parfile, so all the worker process will start in local instance only
2. Remove the parallel option in parfile

I have followed here 1st Solution. After changes done to the parfile import completed successfully.

Parfile After Changes:
***********************
$ more impdp_TABLES.par
directory=refresh
CLUSTER=N
dumpfile=expdp_TABLES_First_Set_%U.dmp
logfile=impdp_TABLES.log
parallel=6
tables=sivaram.TITAN

Reference: MOS 1173765.1

Happy Learning :)

SQLT Installation and Report Generation

SQLT Installation:
*******************
1. Download the sqlt zip file from MOS: 215187.1
2. Copy to the server using scp/winscp
3. Unzip the sqlt zip file to the $ORACLE_HOME/bin directory
$ cp /home/oracle/sqlt_latest.zip /u01/app/oracle/product/12.1.0.2/db_1/bin/
$ unzip sqlt_latest.zip
Archive:  sqlt_latest.zip

4. After unzip completed it will creates the sqlt directory. Go to the sqlt directory
$ cd sqlt/
$ ls -lhrt
total 116K
drwxr-xr-x 3 oracle oinstall 4.0K Nov 13 12:21 input
drwxr-xr-x 2 oracle oinstall 4.0K Nov 20 16:57 install
drwxr-xr-x 2 oracle oinstall 4.0K Nov 20 16:57 run
drwxr-xr-x 7 oracle oinstall 4.0K Nov 20 16:57 utl
drwxr-xr-x 2 oracle oinstall 4.0K Nov 20 16:57 doc
-rw-r--r-- 1 oracle oinstall  56K Dec  6 19:26 sqlt_instructions.html
-rw-r--r-- 1 oracle oinstall  38K Dec  6 19:27 sqlt_instructions.txt

5. Connect to the database as sysdba and run the sqcreate.sql
$ sqlplus /nolog
SQL> connect / as sysdba
Connected.
SQL> start install/sqcreate.sql
It will prompt for the below options just give the respective values

Optional Connect Identifier (ie: @PROD): just type enter don't give any value in case of local server

Password for user SQLTXPLAIN: Proivde the new password for the SQLTXPLAIN user that must satisfy the password verify function otherwise you installation will fail
Re-enter password: Proivde the same above password

Default tablespace [UNKNOWN]: USERS  Provide the tablespace on which you want to install the SQLTXPLAIN user
Temporary tablespace [UNKNOWN]: TEMP Provide the temp tablespace
Main application user of SQLT: SYS
Oracle Pack license [T]: T

Once everything is completed then it will creates the below 2 users. And provide the necessary grants.

SQL> select USERNAME,ACCOUNT_STATUS from dba_users where trunc(CREATED)=trunc(sysdate);
USERNAME                                   ACCOUNT_STATUS
------------------------------------------ --------------------------------
SQLTXADMIN                                 EXPIRED & LOCKED
SQLTXPLAIN                                 OPEN

SQL> alter user SQLTXADMIN identified by SQLTXADMIN account unlock;

User altered.

SQL> grant create connect,resource,SQLT_USER_ROLE to SQLTXPLAIN,SQLTXADMIN;

Grant succeeded.
Note: All SQLT users must be granted the SQLT_USER_ROLE before they can use any of the main methods. If It's not possible to use SYS as SQLT user, SQLTXADMIN needs to be granded INHERIT PRIVILEGES on SYS.

SQL> GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN;

Grant succeeded.

SQLT report Generation for the specific sql_id:
***********************************************
1. Identify the which sql_id you want to generate the sqlt report
2.
SQL> ho pwd
/u01/app/oracle/product/12.1.0.2/db_1/bin/sqlt
SQL> conn SQLTXPLAIN/SQLTXPLAIN
Connected.
SQL> start run/sqltxtract.sql

The script will prompt for the sql_id and SQLTXPLAIN password please provide the same.
............
............
SQLTXTRACT completed.
SQL>exit
The script will generate the zip with the sql_id.
$ ls -lhrt|tail
-rw-r--r-- 1 oracle oinstall  29M Apr 22 03:59 sqlt_s62619_xtract_5xagduq334p7t.zip

Happy Learning :)