Thursday, April 11, 2013

Oracle Database Architecture Diagram


Posted by at 1 comment:

Thursday, April 4, 2013

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
Posted by at No comments:

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.


Posted by at No comments:

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;
/

Posted by at No comments:
Subscribe to: Posts (Atom)