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 :)

Find out the list of users with default passwords in Oracle

From 11g we can quickly identify users with default passwords by querying the  data dictionary view: DBA_USERS_WITH_DEFPWD. This view will list out the users who are currently having the default passwords.

SQL> select username from dba_users_with_defpwd ;

USERNAME
------------------------------
DIP
MDSYS
CTXSYS
OLAPSYS
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA
WMSYS


14 rows selected.

The above list is the users who are currently having the default passwords in the database.

SQL> show user
USER is "SYS"
Now change the password for scott and query the above view that won't show the scott user in the list as you changed the scott user default password.

SQL> alter user scott identified by tiger1;
 
User altered.

Now if you check the view:

SQL> select * from dba_users_with_defpwd;

USERNAME
------------------------------
DIP
MDSYS
CTXSYS
OLAPSYS
OUTLN
EXFSYS
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA

WMSYS

13 rows selected.

To achieve the security compliance for the database, change the password for the above users and then expire the password then very next time when you are connecting to the user that will ask the new password.

SQL> select 'alter user '||username||' identified by Welcome678 account password expire;'from dba_users_with_defpwd where username not in ('SYS','SYSTEM','DBSNMP');

'ALTERUSER'||USERNAME||'IDENTIFIEDBYRESOURCE09ACCOUNTLOCKPASSWORDEXPIRE;'
---------------------------------------------------------------------------
alter user MDSYS identified by Welcome678 account password expire;
alter user OLAPSYS identified by Welcome678 account password expire;
alter user LBACSYS identified by Welcome678 account password expire;
alter user ORDSYS identified by Welcome678 account password expire;
alter user WMSYS identified by Welcome678 account password expire;
alter user XDB identified by Welcome678 account password expire;
alter user SI_INFORMTN_SCHEMA identified by Welcome678 account password expire;
alter user CTXSYS identified by Welcome678 account password expire;
alter user MDDATA identified by Welcome678 account password expire;
alter user ORDPLUGINS identified by Welcome678 account password expire;
alter user DIP identified by Welcome678 account password expire;
alter user EXFSYS identified by Welcome678 account password expire;
alter user OUTLN identified by Welcome678 account password expire;


13 rows selected.

Once you execute the above sql statements then we can say currently no users in the database is using default password.

SQL> select username from dba_users_with_defpwd ;

no rows selected.

Happy Learning :)

RMAN06820: WARNING: FAILED TO ARCHIVE CURRENT LOG AT PRIMARY

RMAN archivelog backup at the standby site is throws the following errors

Logfile Details:
****************
RMAN06820: WARNING: failed to archive current log at primary database 
ORACLE error from target database:
ORA17629: Cannot connect to the remote database server 
ORA-17627: ORA-00942: table or view does not exist

But the rest of the backup actually complete successfully.

CAUSE:
******
11.2.0.4 onward as per 'unpublished' Bug 8740124, we now include the current standby redo log as part of an RMAN archivelog backup at the standby site. This is achieved by forcing a log switch at the primary site. However, the connection to the primary failed when attempting to do so.
This is due to this bug:
Bug 17580082 ACTIVE STANDBY RMAN06820: WARNING: FAILED TO ARCHIVE CURRENT LOG AT PRIMAR

Workaround:
***********
Do not use operating system authentication to login with RMAN. Use a username and password.
That is, do not use just the "/" (operating system authentication) connect to the standby database:
$ rman target /
Connecting as 'rman target /'
# it gets the sys user but not the password and so, it does NOT mean it is being explicitly specified to connect as sysdba.
Instead put in the username and password for the SYSDBA user:
$ rman target sys/password@stby
Connecting as 'rman target sysdba_user/password@stby'
Note: The password, within the password file, for the primary and standby should be identical.

For more details please look at the Doc ID: 1616074.1


Happy Learning :) 

EM12c Metric Evaluation Error – Supplied date is in the future

After the  Daylight Saving Time we are getting below metric collection errors from some host targets.

Metric Evaluation Error Details:
Host=oraprod
Target type=Database Instance
Target name=PROD
Categories=Error
Message= Row(1): Supplied date is in the future : now = Wed Nov 10 10:39:01 EST 2015 supplied value = Wed Nov 10 11:38:07 EST 2015
Severity=Critical
Event reported time=Nov 10, 2015 10:39:01 AM EST
Contact=Sivaram
Location=Banalore
Comment=DB archive
Department=CRM
Operating System=Linux
Platform=x86_64
Event Type=Metric Evaluation Error
Event name=wait_bottlenecks
Metric Group=Wait Bottlenecks
Collection Name=wait_bottlenecks_10i
Rule Name=CRM Metric Evaluation Error Any Target
Rule Owner=SYSMAN
Update Details:
Row(1): Supplied date is in the future : now = Wed Nov 10 10:39:01 EST 2015 supplied value = Wed Nov 10 11:38:07 EST 2015

Solution:
***********

Clearing and restarting agents on these hosts will fix the problem and it will disappear.
$ ps -ef|grep pmon
oracle   15676     1  0 Jul28 ?        00:15:26 ora_pmon_prod
oracle   20606 20239  0 04:07 pts/0    00:00:00 grep pmon
$ . oraenv
ORACLE_SID = [oracle] ? prod
The Oracle base has been set to /u01/app/oracle

$ ps -ef|grep emagent

oracle    3390 20239  0 04:31 pts/0    00:00:00 grep emagent
oracle    9521     1  0 Jul15 ?        00:02:12 /u01/app/oracle/product/12.1.0.2/core/12.1.0.4.0/perl/bin/perl

$ cd /u01/app/oracle/product/12.1.0.2/core/12.1.0.4.0/bin
$ ./emctl stop agent
$ ./emctl stop agent
$ ./emctl clearstate agent
$ ./emctl start agent
$ ./emctl upload agent

Happy Learning :)

How to modify db_file_name_convert parameter value on standby using DGMGRL

1) Check the value of the db_file_name_convert parameter

SQL> show parameter db_file_name_convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      +P1PROD_DATA/p1prod/datafile/,
    +F1PROD_DATA/f1prod/datafile

2. Change the db_file_name_convert  according to the your requirement in sql prompt
SQL> alter system set db_file_name_convert='+P1PROD_DATA/p1prod/datafile/','+F1PROD_DATA/f1prod/datafile/','+P1PROD_DATA2/p1prod/datafile/','+F1PROD_DATA2/f1prod/datafile/' scope=spfile;

Once you change the parameter value in sql prompt then you need to update the same in DGMGRL prompt other wise  you will get the error for the incosistent properties.

DGMGRL> show database f1prod inconsistentproperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
         f1prod    DbFileNameConvert +P1PROD_DATA/p1prod/datafile/, +F1PROD_DATA/f1prod/datafile

DGMGRL> show configuration

Configuration - p1prod_dg

  Protection Mode: MaxPerformance
  Members:
  p1prod - Primary database
    f1prod - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with database setting

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 37 seconds ago)

DGMGRL> show database f1prod

Database - f1prod

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 886.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    f1prod
      Warning: ORA-16675: database instance restart required for property value modification to take effect
      Warning: ORA-16714: the value of property DbFileNameConvert is inconsistent with the database setting

Database Status:
WARNING

3) Change the parameter value in DGMGRL prompt

DGMGRL> edit DATABASE "f1prod" SET PROPERTY DbFileNameConvert='+P1PROD_DATA/p1prod/datafile/,+F1PROD_DATA/f1prod/datafile/,+P1PROD_DATA2/p1prod/datafile/,+F1PROD_DATA2/f1prod/datafile/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "dbfilenameconvert" updated

Once you changed the parameter value then you need to restart the database in order to take the modified values to effect.

4) Stop and start the database using SRVCTL

$ srvctl stop database -d f1prod
[oracle@prodora02:/home/oracle f1prod]>
$ srvctl start database -d f1prod
You have new mail in /var/spool/mail/oracle
[oracle@prodora02:/home/oracle f1prod]>
$ srvctl status database -d f1prod
Database is running.

5) Now you can see that the inconsistent properties value is cleared as after database restart the parameter will effected with the new value.

DGMGRL> show configuration

Configuration - p1prod_dg

  Protection Mode: MaxPerformance
  Members:
  p1prod - Primary database
    f1prod - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 48 seconds ago)

DGMGRL> show database f1prod inconsistentproperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

Happy Learning :)

Convert Physical Standby to Snapshot Standby and Vice Versa using DGMGRL

Check the below commands output before proceeding at standby server.
$ srvctl status database -d stand
$ srvctl status service -d stand
$ srvctl config database -d stand
$ ./crsctl status res -t

Connect to primary server and perform the below steps this will convert the physical standby to snapshot standby

DGMGRL> show configuration;

Configuration - DG-prod

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

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 23 seconds ago)


DGMGRL> convert database stand to snapshot standby;
Converting database "stand" to a Snapshot Standby database, please wait...
Database "stand" converted successfully
DGMGRL> show configuration;

Configuration - DG-prod

  Protection Mode: MaxPerformance
  Members:
  prod   - Primary database
    stand - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 11 seconds ago)

DGMGRL> show database stand;

Database - stand

  Role:               SNAPSHOT STANDBY
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          1 minute 18 seconds (computed 0 seconds ago)
  Instance(s):
    stand

Database Status:
SUCCESS

Make sure that you have the enough space for the FRA.
The below query will give the FRA usagae details.
SQL> 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> select * from V$FLASH_RECOVERY_AREA_USAGE;

Do your tests on the snapshot standby at this time you can open in read-write mode. Once your work done then convert back to snapshot standby to physical standby.

DGMGRL> convert database stand to physical standby;
Converting database "stand" to a Physical Standby database, please wait...
Database "stand" converted successfully
DGMGRL> show configuration;

Configuration - DG-prod

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

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 23 seconds ago)

Once done please crosscheck the sync status between primary and standby.

*********************Check Sync Status**************************

Primary:
*******
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
        127056

Standby:
*******
SQL> select max(sequence#) from V$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
        127056

Also check the all the oracle services are running as before the snapshot convert.

$ srvctl status database -d stand
$ srvctl status service -d stand
$ srvctl config database -d stand
$ ./crsctl status res -t

The outputs of the above commands should match with the previous one. If not we need to troubleshoot it.

Happy Learning :)

How to tune a SQL statement

Please follow the below steps for tuning a SQL statement.

Take the sql_id before going to start.

1. Create Tuning Task:

SQL> set serveroutput on
declare
sql_tune_task_id  varchar2(100);
begin
sql_tune_task_id := dbms_sqltune.create_tuning_task (
                          sql_id      => '03h94xbcu25c3',            //give your sql_id
                          scope       => dbms_sqltune.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sql_tune_03h',
                          description => 'tuning task for statement 03h94xbcu25c3');
  dbms_output.put_line('sql_tune_task_id: ' || sql_tune_task_id);
end;
/

2. Execute Tuning Task:
SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'sql_tune_03h');

3. TO GET SUMMARY INFORMATION:

SQL> set long 100000
set longchunksize 1000
set pagesize 10000
set linesize 100
select dbms_sqltune.report_tuning_task('sql_tune_03h') as recommendations from dual;

The above query result gives the recommendations if you want to follow you can. After completion of your work drop the tuning task which you created earlier.

4. Drop the tuning task:

SQL> begin
DBMS_SQLTUNE.DROP_TUNING_TASK('sql_tune_03h');
end;
 /

To know the status of the tuning task
SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = '<OWNER_NAME>';

You can tune the sql statement by using the below script provided by the Oracle
script location: ORACLE_HOME/rdbms/admin/sqltrpt.sql
It will prompt for the sql_id, provide the sql_id.

Happy Learning :)

Standby Creation step by step using RMAN Active database duplicate

Standby creation for prod by RMAN duplicate concept
Details of servers are given below.
Primary server details:-
Server name: oracleserver01.prod.com
IP Address: 192.168.1.67
SID: prod

Standby server details:-
Server name : oracleserver02.prod-standby.com
IP Address: 192.168.1.68

Note:
******
Before performing standby creation hash out cron for archive deletion from production.
Steps to be followed to create standby for prod.

1. Add standby logfile in production database server(online logfile + 1)

SQL> select b.GROUP#,b.MEMBER,a.BYTES/1024/1024,a.THREAD# from v$log a,v$logfile b where a.GROUP#=b.GROUP#;
    GROUP# MEMBER                                                             A.BYTES/1024/1024    THREAD#
---------- ------------------------------------------------------------------ ----------------- ----------
         3 +DATA/prod/ONLINELOG/group_3.381.881928479                                 50          1
         3 +FLASH/prod/ONLINELOG/group_3.2693.881928479                               50          1
         2 +DATA/prod/ONLINELOG/group_2.382.881928479                                 50          1
         2 +FLASH/prod/ONLINELOG/group_2.2692.881928479                               50          1
         1 +DATA/prod/ONLINELOG/group_1.383.881928477                                 50          1
         1 +FLASH/prod/ONLINELOG/group_1.2691.881928477                               50          1
6 rows selected.

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL

SQL> alter system set standby_file_management='AUTO' scope=both;
System altered.

SQL> show parameter standby
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO

SQL> show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1          string      +DATA
db_create_online_log_dest_2          string      +FLASH
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string

SQL> alter system set db_create_online_log_dest_2='';
System altered.

SQL> show parameter db_create
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1          string      +DATA
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string

SQL> alter database add standby logfile size 50m;
Database altered.
SQL> alter database add standby logfile size 50m;
Database altered.
SQL> alter database add standby logfile size 50m;
Database altered.
SQL> alter database add standby logfile size 50m;
Database altered.

SQL> select b.GROUP#,b.MEMBER,a.BYTES/1024/1024,a.THREAD# from v$log a,v$logfile b;
    GROUP# MEMBER                                                             A.BYTES/1024/1024    THREAD#
---------- ------------------------------------------------------------------ ----------------- ----------
         3 +DATA/prod/ONLINELOG/group_3.381.881928479                                 50          1
         3 +DATA/prod/ONLINELOG/group_3.381.881928479                                 50          1
         3 +DATA/prod/ONLINELOG/group_3.381.881928479                                 50          1
         3 +FLASH/prod/ONLINELOG/group_3.2693.881928479                               50          1
         3 +FLASH/prod/ONLINELOG/group_3.2693.881928479                               50          1
         3 +FLASH/prod/ONLINELOG/group_3.2693.881928479                               50          1
         2 +DATA/prod/ONLINELOG/group_2.382.881928479                                 50          1
         2 +DATA/prod/ONLINELOG/group_2.382.881928479                                 50          1
         2 +DATA/prod/ONLINELOG/group_2.382.881928479                                 50          1
         2 +FLASH/prod/ONLINELOG/group_2.2692.881928479                               50          1
         2 +FLASH/prod/ONLINELOG/group_2.2692.881928479                               50          1
         2 +FLASH/prod/ONLINELOG/group_2.2692.881928479                               50          1
         1 +DATA/prod/ONLINELOG/group_1.383.881928477                                 50          1
         1 +DATA/prod/ONLINELOG/group_1.383.881928477                                 50          1
         1 +DATA/prod/ONLINELOG/group_1.383.881928477                                 50          1
         1 +FLASH/prod/ONLINELOG/group_1.2691.881928477                               50          1
         1 +FLASH/prod/ONLINELOG/group_1.2691.881928477                               50          1
         1 +FLASH/prod/ONLINELOG/group_1.2691.881928477                               50          1
         4 +DATA/prod/ONLINELOG/group_4.430.881932753                                 50          1
         4 +DATA/prod/ONLINELOG/group_4.430.881932753                                 50          1
         4 +DATA/prod/ONLINELOG/group_4.430.881932753                                 50          1
         5 +DATA/prod/ONLINELOG/group_5.431.881932867                                 50          1
         5 +DATA/prod/ONLINELOG/group_5.431.881932867                                 50          1
         5 +DATA/prod/ONLINELOG/group_5.431.881932867                                 50          1
         6 +DATA/prod/ONLINELOG/group_6.432.881932901                                 50          1
         6 +DATA/prod/ONLINELOG/group_6.432.881932901                                 50          1
         6 +DATA/prod/ONLINELOG/group_6.432.881932901                                 50          1
         7 +DATA/prod/ONLINELOG/group_7.433.881932905                                 50          1
         7 +DATA/prod/ONLINELOG/group_7.433.881932905                                 50          1
         7 +DATA/prod/ONLINELOG/group_7.433.881932905                                 50          1

30 rows selected.

2. Enable force logging on production.
SQL> alter database force logging;

SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES

3. In the standby database home, create and start a listener that offers a static SID entry for the standby database.
[oracle@oracleserver02 admin]$ more /u01/app/12.1.0.2/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/12.1.0.2/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
          (SID_LIST =
            (SID_DESC =
              (GLOBAL_NAME = standby_dgmgrl)
              (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
              (SID_NAME = standby)
            )
          (SID_DESC =
              (GLOBAL_NAME = standby)
              (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
              (SID_NAME = standby)
            )
          )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

Tnsnames.ora
***********
prod =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
LISTENER_prod =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))


standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )

LISTENER_standby =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))

4. In the database home on the primary node, create an Oracle Net alias to connect to the listener created in the above step.
prod listener:
**************
[oracle@oracleserver01 admin]$ more /u01/app/12.1.0.2/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/12.1.0.2/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = prod)
      (ORACLE_HOME =/u01/app/oracle/product/12.1.0.2/db_1)
    )
  )
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

Tnsnames.ora:
************
standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )
LISTENER_standby =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
prod =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
LISTENER_prod =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))

5. Register the new listener with new grid home(12.1*)

$ srvctl modify listener –l listener –o /u01/app/12.1.0.2/grid

[oracle@oracleserver02 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-JUN-2015 13:54:43
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver02.prod-standby.com)(PORT=1521)))
The command completed successfully

[oracle@oracleserver02 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-JUN-2015 13:54:53
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver02.prod-standby.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                16-MAY-2015 03:22:03
Uptime                    24 days 10 hr. 32 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracleserver02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver02.prod-standby.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "standby_DGB" has 1 instance(s).
  Instance "standby", status READY, has 1 handler(s) for this service...
Service "standby" has 2 instance(s).
  Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
  Instance "standby", status READY, has 1 handler(s) for this service...
Service "standbyXDB" has 1 instance(s).
  Instance "standby", status READY, has 1 handler(s) for this service...
Service "standby" has 1 instance(s).
  Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Check for network connectivity from primary to standby database.

6. Create a password file on the standby host that where the auxiliary instance will be started. Copy password file from primary to standby in $ORACLE_HOME/dbs location.
[oracle@oracleserver02 dbs]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/dbs
[oracle@oracleserver02 dbs]$ orapwd file=orapwstandby password=yas_3

7.  On the standby host in the ORACLE_HOME/dbs directory create a pfile (initstandby.ora) with the following parameters:
[oracle@oracleserver02 dbs]$ more initstandby.ora
db_name=prod
db_unique_name=standby
sga_target=2g
db_create_online_log_dest_1='+DATA'
db_create_file_dest='+DATA'
db_recovery_file_dest_size=5g
db_recovery_file_dest='+FLASH'
control_files='+DATA'
compitable=12.1.0.2

8.create audit directory on for standby database on standby server
mkdir –p /u01/app/oracle/admin/standby/adump

9. On the standby host set the SID to the same as standby and startup nomount the standby/auxiliary instance. And add the the sid in oratab file too.

export ORACLE_SID=standby
sqlplus / as sysdba
startup nomount
[oracle@oracleserver02 admin]$cat /etc/oratab
standby: /u01/app/oracle/product/12.1.0.2/db_1/:N

10. On the primary host run an rman script that duplicates the primary database using the “duplicate target database for standby from active database” command

[oracle@oracleserver01 dba]$ more rman_standby_standby.ksh
!/bin/ksh

export ORAENV_ASK="NO"
export ORACLE_SID=prod
. oraenv
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

rman target sys/yas_3@prod auxiliary sys/yas_3@standby debug trace=rman_trace.log log=standby_attempt.log<<EOF
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stdby1 type disk;
allocate auxiliary channel stdby2 type disk;

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET DB_UNIQUE_NAME='standby'
set db_file_name_convert='+FLASH/prod/','+FLASH/standby/','+DATA/prod','+DATA/standby','prod','standby'
set log_file_name_convert='+FLASH/prod/','+FLASH/standby/','+DATA/prod','+DATA/standby','+DMNO_REDO/prod','+DMNO_REDO/standby','prod','standby'
set control_files='+DATA/standby/controlfile/control01.ctl','+FLASH/standby/controlfile/control02.ctl'
set  DB_CREATE_FILE_DEST='+DATA'
set db_recovery_file_dest='+FLASH'
set db_recovery_file_dest_size='200G'
set audit_file_dest='/u01/app/oracle/admin/standby/adump'
Nofilenamecheck;
release channel prmy1;
release channel prmy2;
release channel stdby1;
release channel stdby2;
}
EOF

A brief explanation of the individual clauses is shown below.

• FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
• FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
• DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
• SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
• NOFILENAMECHECK: Destination file locations are not checked.

10. create Softlink for network files.

ln -s /u01/app/12.1.0.2/grid/network/admin/listener.ora listener.ora
ln -s /u01/app/12.1.0.2/grid/network/admin/tnsnames.ora tnsnames.ora

12. Edit the parameter log_archive_dest_2 in primary database.
Primary:
*******
SQL> alter system set fal_server=standby;
System altered.
SQL> alter system set fal_client=prod;
System altered.
SQL> alter system set log_archive_config='dg_config=(prod,standby)';
System altered.

SQL> alter system set log_archive_dest_2= ‘service="standby" ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="standby" net_timeout=30, valid_for=(online_logfile,all_roles)’;

13. Set below parameters on standby.

Standby:
*******
SQL> alter system set fal_server=standby;
System altered.
SQL> alter system set fal_client=prod;
System altered.
SQL> alter system set log_archive_config='dg_config=(prod,standby)';
System altered.

13. The standby database will get mounted.
SQL> alter database recover managed standby database using current logfile disconnect from session;

DGMGRL Configuration:
*********************
1. Login into primary database (prod) server and standby database (standby) server.
2. Check if both the production and standby database are in sync.
On PRIMARY:
? Select max(sequence#) from v$archived_log;
On STANDBY:
? Select max(sequence#) from v$log_history;
3. Check if output of both these matches with each other. If not kindly bring the database in sync, check if there are any errors transfering archives on standby database.
4. Check ‘ERROR’ column in v$managed_standby for any issue related to log transfer
? Select dest_id,error from v$managed_standby;
5. If everything is fine, then proceed with dataguard broker configuration.
6. Start the Dataguard broker DMON process.
      On Primary and Standby,

SQL> alter system set dg_broker_start=true sid='*';
SQL> alter system set dg_broker_config_file1=’<DIR_PATH/file_name.dat>’
SQL> alter system set dg_broker_config_file2=’<DIR_PATH/file_name.dat>’

7. Connect to dataguard broker.
  Connect to DGMGRL

  Primary,
$dgmgrl
DGMGRL>connect /

8. Create configuration.
DGMGRL> CREATE CONFIGURATION 'DG-PRIM' AS PRIMARY DATABASE IS 'prod' CONNECT IDENTIFIER IS prod;

Before performing the above kindly make sure no log_archive_dest_n is set on both Primary and Standby database.
9. Verify configuration.
DGMGRL> show configuration;

Configuration – DG-PRIM

  Protection Mode: MaxPerformance
  Databases:
  boston - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Add Standby
DGMGRL> add database standby as connect identifier is standby;

DGMGRL> show configuration;

Configuration – DG-PRIM

  Protection Mode: MaxPerformance
  Databases:
  prod  - Primary database
    standby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

10. Enable Configuration
DGMGRL> enable configuration;

11. Verify configuration
DGMGRL> show configuration;

Configuration – DG-PRIM

  Protection Mode: MaxPerformance
  Databases:
  prod  - Primary database
    standby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
DGMGRL> show database prod;

Database – prod

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

Database Status:
SUCCESS

DGMGRL>  show database standby;

Database – standby

  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:        2.54 MByte/s
  Real Time Query:   ON
  Instance(s):
    standby
Database Status:
SUCCESS

Note:
******
You can find above steps in Oracle DOC ID: 1583588.1
For prerequisites follow oracle doc ID:  1305019.1

Metrics Average Users Waiting Count is at x for event class Application

If you receive this alert check the sessions which are in waiting state and what is reason behind for the waiting state. The below query will give the list of sessions which are in waiting state. So based on the output you can easily find out.

col LOGONDATE for a15 trunc
col MODULE for a30 trunc
col OSUSER for a8
col USERNAME for a8
set pages 10000
col sql_cost for 9999999 trunc
col sid for 99999999
col sql_CNT for 9999999
col machine for a10
col lc_et for 999999
col spid form a6
col app_id form a6
col event for a30
col wait_mins for 99999 trunc
select /*a.sid,a.serial#,*/ b.spid,a.sql_id,(select OPTIMIZER_COST from v$sqlarea where sql_id=a.sql_id) sql_cost,
(select count(1) from v$session where SQL_HASH_VALUE=a.sql_hash_value) sql_CNT,
a.PROCESS App_ID, a.username,
to_char(a.logon_time,'DD-MM HH24:MI:SS') LogonDate, a.event, a.osuser, a.module,a.machine,round(a.last_call_et/60) wait_mins
from v$session a, v$process b where a.paddr = b.addr
and a.status = 'ACTIVE' and a.username is not null and a.sql_id is not null
order by 8,3,11 desc;

Happy Learning :)

How to drop a disk from the diskgroup

SQL> select name,group_number,state,type,total_mb/1024 "Totalspace(GB)",free_mb/1024 "Freespace(GB)", (free_mb/total_mb)*100 "PCT_FREE" from v$asm_diskgroup; 
NAME                           GROUP_NUMBER STATE       TYPE   Totalspace(GB) Freespace(GB)   PCT_FREE
------------------------------ ------------ ----------- ------ -------------- ------------- ----------
PROD_RECO1                                3 MOUNTED     EXTERN           1600    1305.19922 81.5749512

SQL> Select path,disk_number,name,mount_status,header_status,os_mb/1024,total_mb/1024,free_mb/1024 from v$asm_disk where group_number=3;

PATH                  DISK_NUMBER NAME               MOUNT_S HEADER_STATU OS_MB/1024 TOTAL_MB/1024 FREE_MB/1024
--------------------- ----------- ------------------ ------- ------------ ---------- ------------- ------------
ORCL:ASM_PROD_133               0 ASM_PROD_133       CACHED  MEMBER              400           400   326.292969
ORCL:ASM_PROD_134               5 ASM_PROD_134       CACHED  MEMBER              400           400   326.273438
ORCL:ASM_PROD_135               6 ASM_PROD_135       CACHED  MEMBER              400           400     326.3125
ORCL:ASM_PROD_136               7 ASM_PROD_136       CACHED  MEMBER              400           400   326.320313

SQL> alter diskgroup PROD_RECO1 drop disk ASM_PROD_133;

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           3 REBAL RUN           1          1       1046      31956       5837           5
  
SQL> select name,group_number,state,type,total_mb/1024 "Totalspace(GB)",free_mb/1024 "Freespace(GB)", (free_mb/total_mb)*100 "PCT_FREE" from v$asm_diskgroup;

NAME                           GROUP_NUMBER STATE       TYPE   Totalspace(GB) Freespace(GB)   PCT_FREE
------------------------------ ------------ ----------- ------ -------------- ------------- ----------
PROD_RECO1                                3 MOUNTED     EXTERN           1200    901.734375 75.1445313
   
SQL> Select path,disk_number,name,mount_status,header_status,os_mb/1024,total_mb/1024,free_mb/1024 from v$asm_disk where group_number=3;

PATH                      DISK_NUMBER NAME           MOUNT_S HEADER_STATU OS_MB/1024 TOTAL_MB/1024 FREE_MB/1024
------------------------- ----------- -------------- ------- ------------ ---------- ------------- ------------
ORCL:ASM_PROD_134                   5 ASM_PROD_134   CACHED  MEMBER              400           400   301.148438
ORCL:ASM_PROD_135                   6 ASM_PROD_135   CACHED  MEMBER              400           400   301.167969
ORCL:ASM_PROD_136                   7 ASM_PROD_136   CACHED  MEMBER              400           400   301.164063


Happy Learning :)