Oracle Database Architecture Diagram

GRID CONTROL AGENT OUT OF SYNC

                          Agent Out Of Sync

Error Messages you may see:

Did not receive valid response to ping "ERROR-Agent is blocked. Blocked reason is: Agent is out-of-sync with repository. This most likely means that the agent was reinstalled or recovered. Please contact an EM administrator to unblock the agent by performing an agent resync from the console. Please contact EM adminstrator to unblock the agent"

'OR'

Error: The Oracle Management Server(OMS) has blocked this agent because it has either been reinstalled or restored from a filesystem backup. Please click on the Agent Resynchronization button to resync the agent.

Reason:

The communication between OMS and agent does not work. This is in most cases because something has been changed to one of the components so the SSL certificate is not valid anymore. Therefore you have to resecure the agent.

Try these solutions in the following order:

*****************************************
* Resync Agent

From the Grid Control console, click on the Agent target.

Select "Agent Resynchronization" from the bottom menu.

*****************************************
* Run the following commands for Agent Clearstate.

set ORACLE_HOME=E:\oracle\agent11g
set PATH=%ORACLE_HOME%\bin;%PATH%

emctl status agent

emctl stop agent

emctl clearstate agent

emctl start agent

emctl upload agent

emctl status agent

******************************************
* Remove all of the following from the Agent Home (for UNIX/LINUX- rm *.* under each directory)

del /Q E:\oracle\Middleware\agent11g\sysman\emd\state\*
del /Q E:\oracle\Middleware\agent11g\sysman\emd\upload\*
del E:\oracle\Middleware\agent11g\sysman\emd\lastupld.xml
del E:\oracle\Middleware\agent11g\sysman\emd\agntstmp.txt
del E:\oracle\Middleware\agent11g\sysman\emd\protocol.ini
del E:\oracle\Middleware\agent11g\sysman\emd\blackouts.xml

set ORACLE_HOME=E:\oracle\agent11g
set PATH=%ORACLE_HOME%\bin;%PATH%

emctl stop agent
emctl start agent
emctl status agent

RMAN-03009: ORA-19504: ORA-27040 OSD-04002:

Error Creating a Control File Backup :

Starting Control File Autobackup at 28-MAR-13
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of Control File Autobackup command on c1 channel at 03/28/2013 23:34:12
ORA-19504: failed to create file "E:\ORABACK\VMPROD\BACKUP\BACKUPDATA\RMAN
BACKUP\SNAP_CTLFILE.CTL"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

Recovery Manager complete.

Workaround:

RMAN>show all; 

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name VMPROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\oraback\VMPROD\backup\backupdata\rman
backup\snap_ctlfile.ctl'; - during a manual RMAN run these parameters were saved to RMAN configuration
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\ORABACK\VMPROD\BACKUP\BACKUPDATA\RMAN
BACKUP\SNAP_CTLFILE.CTL'; --- during a manual RMAN run these parameters were saved to RMAN configuration

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME CLEAR; -- restores to default RMAN parameter values

old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\oraback\VMPROD\backup\backupdata\rmanbackup\snap_ctlfile.ctl';
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\ORABACK\VMPROD\BACKUP\BACKUPDATA\RMANBACKUP\SNAP_CTLFILE.CTL';
RMAN configuration parameters are successfully reset to default value

RMAN> show all;

RMAN configuration parameters for database with db_unique_name VMPROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\ORACLE\ORA11GR2\DATABASE\SNCFVMPROD.O
RA'; # default

After clearing the above control file parameters from RMAN configuration, try running the RMAN backup job again.

TRIGGER BEFORE DROPPING AND REPLACING AN EXISTING SYNONYM

Before Drop:

CREATE OR REPLACE TRIGGER 'ABC'
BEFORE DROP
ON DATABASE
DECLARE
GRANTED_ROLE VARCHAR2(30);
X_GRANTED_ROLE VARCHAR2(100);
temp_cnt NUMBER  := 0 ;
BEGIN

select count(*)
INTO temp_cnt
from dba_users
where profile LIKE 'APPLICATION%' --- here anyone can drop except, users with Application profile                                    
and username = ora_dict_obj_owner ;  

if ( temp_cnt > 0 )
    THEN
    IF SYS.DICTIONARY_OBJ_TYPE = 'SYNONYM' THEN
        RAISE_APPLICATION_ERROR(-20998, 'Public Synonym Cannot be dropped. Contact DBA');
    END IF;
  END IF ;
  END;
/


Before Replace: (Can create a new synonym, but cannot replace an existing synonym)


1)
CREATE OR REPLACE TRIGGER 'XYZ'
  before create on database
declare
    temp_cnt NUMBER := 0 ;

  function syn_exists
    return boolean is
    v_dummy   varchar2 (1);
    raise_application_error varchar2(30);
  begin
    select null
    into   v_dummy
    from   dba_synonyms
    where      owner = 'PUBLIC'
           and synonym_name = ora_dict_obj_name;

    return true;
  exception
    when no_data_found then
      return false;
  end syn_exists;
begin

select count(*)
INTO temp_cnt
from dba_users
where profile LIKE 'APPLICATION%'  ---- for certain users in DB
and username = ora_dict_obj_owner ;  

if ( temp_cnt > 0 ) and ( ora_dict_obj_type = 'SYNONYM' ) and (syn_exists ) then
        raise_application_error ( -20000, 'Public Synonym ' || ora_dict_obj_name || ' aready exists. Cannot replace it. Contact DBA');
end if ;

end;
/

'OR'

2)

CREATE OR REPLACE TRIGGER '123'

  before create on database

DISABLE

declare

  function syn_exists

    return boolean is

    v_dummy   varchar2 (1);

    raise_application_error varchar2(30);

  begin

    select null

    into   v_dummy

    from   all_synonyms

    where      owner = ora_dict_obj_owner

           and synonym_name = ora_dict_obj_name;

    return true;

  exception

    when no_data_found then

      return false;

  end syn_exists;

begin

  if ora_dict_obj_type = 'SYNONYM' then

    if syn_exists then

      if not dbms_session.is_role_enabled ('DBA') then --- for all users in DB

        raise_application_error ( -20000, 'Synonym ' || ora_dict_obj_name || ' aready exists, attempt to replace contact DBA');

      end if;

    end if;

  end if;

end;

/

ASMCMD-08102: NO CONNECTION TO ASM; COMMAND REQUIRES ASM TO RUN

Platform         Microsoft Windows x64 (64-bit)
Product Version  11.2.0.2

DETAILED PROBLEM DESCRIPTION

+ASM instance is up/running and only accessible through EMGrid Control and cannot use 

sqlplus to connect to +ASM instance, but asmcmd fails with the following error:

 
ASMCMD-08102: no connection to ASM; command requires ASM to run

C:\Users\vmummadi>set oracle_home=C:\Oracle\Grid11gR2
C:\Users\vmummadi>SET ORACLE_SID=+ASM
C:\Users\vmummadi>asmcmd
Connected to an idle instance.
ASMCMD> lsdg
ASMCMD-08102: no connection to ASM; command requires ASM to run

 Enabled asmcmd tracing and the trace further shows:

C:\Users\vmummadi>set DBI_TRACE=1
C:\Users\vmummadi>set oracle_home=C:\Oracle\Grid11gR2
C:\Users\vmummadi>asmcmd
   DBI 1.602-ithread default trace level set to 0x0/1 (pid 7412) at DBI.pm line 273 via asmcmdshare.pm line 201
   -> DBI->connect(dbi:Oracle:, , ****, HASH(0x44ca968))
   -> DBI->install_driver(Oracle) for MSWin32 perl=5.010000 pid=7412 ruid=0 euid=0
      install_driver: DBD::Oracle version 1.20 loaded from C:/Oracle/Grid11gR2/perl/site/lib/DBD/Oracle.pm
            !! ERROR: '12560' 'ORA-12560: TNS:protocol adapter error (DBD ERROR: OCIServerAttach)' (err#0)
         DBI connect('','',...) failed: ORA-12560: TNS:protocol adapter error (DBDERROR: OCIServerAttach)
Connected to an idle instance.
ASMCMD>

check listener:

C:\Users\vmummadi>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 07-MAR-2013 08:49:35
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
---------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date                22-FEB-2013 20:39:32
Uptime                    12 days 12 hr. 10 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\Oracle\Grid11gR2\network\admin\listener.ora
Listener Log File         C:\Oracle\diag\tnslsnr\ATLAS\listener\alert\log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<omitted here>)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<omitted here>)(PORT=1521)))
Services Summary...
Service "+asm" has 1 instance(s).
 Instance "+asm", status READY, has 1 handler(s) for this service...
...
The command completed successfully

Errors:

The errors are misleading, including the ORA-12560 (TNS:protocol adapter error).

The clue to the underlying problem is coming from the ORA-1031 (insufficient privileges) 

during sqlplus "/ as sysasm"

C:\> set oracle_home=C:\Oracle\Grid11gR2

C:\> SET ORACLE_SID=+ASM
C:\> echo %ORACLE_SID%
+ASM
C:\> echo %ORACLE_HOME%
C:\Oracle\Grid11gR2
C:\> sqlplus "/ as sysasm"
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 13 12:03:09 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges 

Also, check if "SQLNET.AUTHENTICATION_SERVICES = (NTS)" is already set in the server's 

sqlnet.ora file

Solutions:

User is not part of the ORA_DBA OS group:

C:\Users\vmummadi>echo %username%
vmummadi
C:\Users\vmummadi>NET LOCALGROUP ORA_DBA
Alias name     ORA_DBA
Comment        Oracle DBA Group
Members
-------------------------------------------------------------------------------
.... <-- other users listed here except 'vmummadi'

NT AUTHORITY\SYSTEM
The command completed successfully.

The problem is to do with harden security in 11g that affects non-dba OS users with 

connections to Oracle instances.

If the user is not a member of the ORA_DBA OS group, then it either needs to connect to 

the ASM instance using the ASM orapw file (either using his/her own account or using the 

Oracle SYS account and as sysasm privilege) or be added to the OS ORA_DBA group.

Other workarounds include :

Add the non-dba OS user to the ASM's orapw file and grant it 'SYSASM' privileges:
Follow this -                                                                                                                                     http://docs.oracle.com/cd/E11882_01/server.112/e25494/dba007.htm#ADMIN12478
Allow the non-dba user to connect to the ASM instance using the SYS Oracle user account 
(for this, the non-dba user must know what is the password for SYS): 

Example: sqlplus sys@<hostname or IP address of the host>:<port were the listener
lis  tens>/+ASM as sysasm
Make the non-dba OS user a member of the ORA_DBA OS group so it can use bequeath connections 
(ie, not using the listener) to the asm instance (ie, sqlplus "/ as sysasm")                                                                                                                                                                       
             Oracle Support -ID 1537484.1   Reference: Bug 16470328 

Using Snapshot Standby Database On Oracle 11g

A snapshot standby database is a fully update-able standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot Standby is open in the read-write mode and hence it is possible to process transactions independently of the primary database. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use. 

Snapshot database has following characteristics

1. Snapshot standby database receives and archives, but does not apply the redo data.

2. Redo data received from the primary database is applied automatically once it is converted back into a physical standby database.

3. Data from the primary database is always protected as the archives are being received and stored in place.

4. All local updates will be discarded when snapshot database is converted back to physical standby database.

5. If the primary database moves to new database branch (for example, because of a Flashback Database or an OPEN RESETLOGS), the snapshot standby database will continue accepting redo from new database branch.

6. Snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.

7. After a switchover or failover between the primary database and one of the physical or logical standby databases in a configuration, the snapshot standby database can receive redo data from the new primary database after the role transition.

8. Snapshot standby database cannot be the only standby database in a Maximum Protection Data Guard configuration.

Once the snapshot standby is activated this database diverges from its primary database over time because redo data from the primary database is not applied.Again local updates to the snapshot standby database will cause additional divergence.

--> Steps to convert Physical Standby Database to the Snapshot Standby Database:

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;  

1) If not already configured , configure flash recovery area as given below 

a) Set the size for recovery area. 

Alter system set db_recovery_file_dest_size=<size> 

b) Set Flash recovery area. 

Alter system set db_recovery_file_dest=<path> 

2) Bring the physical standby database to mount stage.  

3) Stop managed recovery if it is active.  

4) Convert physical standby database to snapshot standby database.  

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;  

The database is dismounted during conversion and must be restarted. 

Once the database is restarted  any transaction can be executed . 

SQL> select open_mode,database_role from v$database; 

OPEN_MODE                                  DATABASE_ROLE 
----------                                         ---------------- 
READ WRITE                                 SNAPSHOT STANDBY

Remember a guaranteed restore point is created when a physical standby database is converted into a snapshot standby database and this restore point is used to flashback a snapshot standby to its original state when it is converted back into a physical standby database.

--> Steps to convert the Snapshot Standby Database to the Physical Standby Database


1. Shutdown the snapshot standby database. 

2. Bring the database to the mount stage. 

3. Issue the command 

ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 

4. Shutdown the database and mount it

SQL> select open_mode,database_role from v$database; 

OPEN_MODE        DATABASE_ROLE 
----------              ---------------- 
MOUNTED          PHYSICAL STANDBY 

5. Start the media recovery process.

Once a snapshot standby database has been converted back into a physical standby database and restarted, Redo Apply can be started and all redo received by the snapshot standby database will be applied to the physical standby database.

Flashback Database is used to convert a snapshot standby database back into a physical standby database. Any operation that cannot be reversed using Flashback Database technology will prevent a snapshot standby from being converted back to a physical standby.

If you are using the Data Guard Broker, you should use the below command to convert a physical standby database to a snapshot standby database and back.

DGMGRL> CONVERT DATABASE <db_unique_name> TO {SNAPSHOT | PHYSICAL} STANDBY;

Understand and Analyse AWR Report

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. 
Compare the AWR report for the period with bad performance with that of a one taken when performance was good, also a lot depends on type of problem you have, your system, your application, time of day etc.


SNAPSHOTS:

Creating Snapshots From Grid Control(automatically generated every hour), go to:

DB instance-Server tab-Automatic Workload Repository-Run AWR report- By Snapshot(Begin & End)

Creating Snapshots Manually:

You can manually create snapshots with the CREATE_SNAPSHOT procedure if you want to capture statistics at times different than those of the automatically generated snapshots. For example:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

Begin with:
1) Top 5 Timed Foreground Events
2) Operating System Statistics -- how much busy and wait ...
3) SQL statistics -- like top SQL's
4) Instance Activity 
5) I/O stats.
6) look out at advisors (memory-sga,pga, Undo & so on)
7) Buffer waits
8) Instance Efficiency Percentages (Target 100%)

Also check for SQL Statistics:

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by User I/O Wait Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Physical Reads (UnOptimized)
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory
• SQL ordered by Version Count
• Complete List of SQL Text

Reference:
NOTE:1086120.1 - Quick Instructions For Obtaining The Automatic Workload Repository (AWR) Report
FAQ: How to Use AWR reports to Diagnose Database Performance Issues [ID 1359094.1]
Using AWR/Statspack reports to help solve some Portal Performance Problems scenarios [ID 565812.1]