RMAN not conncetd

RMAN NOT CONNECTING PROBLEM



While you are trying to connect RMAN if you get this error 
please follow below procedure

PROBLEM
***********

C:\Users\IBM_ADMIN>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 30 12:32:03 2014

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

RMAN-00571: ==================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS 
RMAN-00571: ========================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges



SOLUTION
*****************

RMAN
connect target sys
connect "/as sysdba" 

FIND RECENTLY WHO and WHAT UPDATED in DATABASE

FIND RECENTLY WHO and WHAT UPDATED in DATABASE


SELECT  s.sid,s.CLIENT_INFO,s.schemaname,s.MACHINE,s.PROGRAM,s.TYPE,s.logon_time,s.osuser,sq.sorts,sq.DISK_READS,sq.BUFFER_GETS,sq.ROWS_PROCESSED,sq.SQLTYPE ,sq.SQL_TEXT FROM v$session s , v$sql sq where s.logon_time BETWEEN to_date('28-MAY-14') and to_date('29-MAY-14');



SELECT a.USERNAME, b.sql_text, a.STATUS FROM V$SESSION a INNER JOIN V$SQLAREA b ON a.SQL_ID = b.SQL_ID;



***********************************************************
******* FIND LAST UPDATED QUERIES WITH USERNAME ***********
***********************************************************


SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME,
         s.program || '-' || s.terminal || '(' || s.machine || ')' PROG,
         s.sid || '/' || s.serial# sid,
         s.status "Status",
         p.spid,
         sql_text sqltext
    FROM v$sqltext_with_newlines t, V$SESSION s, v$process p
   WHERE     t.address = s.sql_address
         AND p.addr = s.paddr(+)
         AND t.hash_value = s.sql_hash_value
ORDER BY s.sid, t.piece;



##################################################

SELECT CREATED, TIMESTAMP, last_ddl_time
  FROM all_objects
 WHERE     OWNER = 'CMICH'
       AND OBJECT_TYPE = 'TABLE'
       AND OBJECT_NAME = 'QP_QP_SETTLEMENT_BATCH';

AUDIT EXAMPLES with queries

AUDIT Examples
*****************


SELECT username, extended_timestamp, owner, obj_name, action_name FROM   dba_audit_trail WHERE  owner = 'CMICH' and action_name='DELETE' ORDER BY timestamp;


1.SELECT username, extended_timestamp, owner, obj_name, action_name FROM   dba_audit_trail WHERE  owner = 'CMICH' and obj_name='QP_SETTLEMENT_BATCH' ORDER BY timestamp;


2.SELECT db_user,extended_timestamp, object_schema, object_name, action FROM   v$xml_audit_trail WHERE  object_schema = 'QP_AUTH' ORDER BY extended_timestamp;


3.SELECT sql_text FROM   dba_fga_audit_trail;



BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'AUDIT_TEST',
    object_name     => 'EMP',
    policy_name     => 'SALARY_CHK_AUDIT',
    audit_condition => 'SAL > 50000',
    audit_column    => 'SAL',
    handler_schema  => 'AUDIT_TEST',
    handler_module  => 'FIRE_CLERK',
    enable          => TRUE);
END;
/


For other auditing options please refer my blog

FIND privillages of a TABLE and SCHEMA and ROLE

VIEWs and QUERIES to find  privileges of a TABLE and SCHEMA and ROLE


#################### VIEWS ################

ALL_COL_PRIVS
ALL_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD
ALL_REPGROUP_PRIVILEGES
ALL_TAB_PRIVS
ALL_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD


USER_AQ_AGENT_PRIVS
USER_COL_PRIVS
USER_COL_PRIVS_MADE
USER_COL_PRIVS_RECD
USER_REPGROUP_PRIVILEGES



USER_ROLE_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD


DBA_ROLE_PRIVS
ALL_ROLE_PRIVS


DBA_ROLE_PRIVS - Roles granted to users and roles
ROLE_ROLE_PRIVS - Roles which are granted to roles
ROLE_SYS_PRIVS - System privileges granted to roles
ROLE_TAB_PRIVS - Table privileges granted to roles


set heading off
set pages 0
set long 9999999
select dbms_metadata.get_granted_ddl('ROLE_GRANT', user)  from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', user)  from dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', user)  from dual;




select * from dba_role_privs where GRANTEE=<SCHEMA NAME>; ------------------ For DBA privillage roles
select * from dba_sys_privs where GRANTEE=<SCHEMA NAME>; -------------------- For system Privillages
select * from dba_tab_privs where GRANTEE=<SCHEMA NAME> and TABLE_NAME=<TABLE NAME>; --------------------- For table Privilllages



******************* Find the ROLES privillages ****************

select * from DBA_ROLE_PRIVS where role='ROLE NAME';
select * from ROLE_ROLE_PRIVS where role='ROLE NAME';
select * from ROLE_SYS_PRIVS where role='ROLE NAME';
select * from ROLE_TAB_PRIVS where role='ROLE NAME';

SIMPLE about AUDIT

BRIEF about AUDIT


Auditing is the monitoring and recording of selected user database actions. To perform auditing, you must be granted the appropriate system privileges

privillages
****************
AUDIT_ADMIN
ADDIT_VIEWER


2WAYS of AUDIT
*****************
SESSION

ACCESS-----STATEMENT LEVEL
       AUDIT ALL BY <username> BY ACCESS WHENEVER SUCCESSFUL/NOT SUCCESSFULL;
       AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY <username> BY ACCESS WHENEVER SUCCESSFUL/NOT SUCCESSFULL;
       AUDIT EXECUTE PROCEDURE BY <username> BY ACCESS WHENEVER SUCCESSFUL/NOT SUCCESSFULL;

note:By ACCESS and WHENEVER SUCCESSFUL/NOT SUCCESSFULL is optional


CHECK audit is enabled/not
************************
sqlplus / as sysdba
show parameter audit


AUDIT PARAMETERS
*******************
Audit_TRAIL = NONE/DB/OS/XML/DB_EXTENDED/XML_EXTENDED
Audit_file_dest = <path audit destination>
Audit_sys_operations = TRUE/FALSE
       to chage these parameters follow this 
ALTER SYSTEM SET  Audit_TRAIL = < NONE/DB/OS/XML/DB_EXTENDED/XML_EXTENDED > SCOPE=BOTH/SPFILE/PFILE;
ALTER SYSTEM SET Audit_file_dest = < path where u want audit files >  SCOPE=BOTH/SPFILE/PFILE
ALTER SYSTEM SET  Audit_sys_operations = TRUE/FALSE SCOPE=BOTH/SPFILE/PFILE

NOTE:when ever chaged the audit parameters u need to RESTART the DB



enable audit
***************
ALTER SYSTEM SET audit_sys_operations=TRUE COMMENT='Begin auditing SYS' SCOPE=BOTH;


Audit VIEWS
*************
DBA_AUDIT_TRAIL--------------------view to check the auditing
AUD$-------------------------------------view 
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS

STATEMENT level AUDIT options in a DATABASE

STATEMENT level AUDIT options in a DATABASE

 Enable AUDIT options for a USER in DB

AUDIT ALTER ANY INDEX, 
ADMINISTER ANY SQL TUNING SET, 
ADMINISTER DATABASE TRIGGER, 
ADMINISTER SQL MANAGEMENT OBJECT, 
ADMINISTER SQL TUNING SET, ADVISOR, 
ALTER ANY CLUSTER, 
ALTER ANY CUBE, 
ALTER ANY CUBE DIMENSION, 
ALTER ANY DIMENSION, 
ALTER ANY EDITION, 
ALTER ANY INDEXTYPE, 
ALTER ANY LIBRARY, 
ALTER ANY MATERIALIZED VIEW, 
ALTER ANY MINING MODEL, 
ALTER ANY OPERATOR, 
ALTER ANY OUTLINE,  
ALTER ANY PROCEDURE, 
ALTER ANY ROLE, 
ALTER ANY SEQUENCE, 
ALTER ANY SQL PROFILE, 
ALTER ANY TABLE, 
ALTER ANY TRIGGER, 
ALTER ANY TYPE, 
ALTER DATABASE, 
ALTER DATABASE LINK, 
ALTER PROFILE, 
ALTER PUBLIC DATABASE LINK, 
ALTER RESOURCE COST, 
ALTER ROLLBACK SEGMENT, 
ALTER SESSION, 
ALTER SYSTEM, 
ALTER TABLESPACE, 
ALTER USER, 
ANALYZE ANY, 
ANALYZE ANY DICTIONARY, 
AUDIT ANY, 
AUDIT SYSTEM, 
BACKUP ANY TABLE, 
BECOME USER, 
CHANGE NOTIFICATION, 
COMMENT ANY MINING MODEL,
 COMMENT ANY TABLE,
 CREATE ANY ASSEMBLY, 
CREATE ANY CLUSTER, 
CREATE ANY CONTEXT, 
CREATE ANY CUBE, 
CREATE ANY CUBE BUILD PROCESS, 
CREATE ANY CUBE DIMENSION, 
CREATE ANY DIMENSION, 
CREATE ANY DIRECTORY, 
CREATE ANY EDITION, 
CREATE ANY INDEX, 
CREATE ANY INDEXTYPE, 
CREATE ANY JOB, 
CREATE ANY LIBRARY,
 CREATE ANY MATERIALIZED VIEW, 
CREATE ANY MEASURE FOLDER, 
CREATE ANY MINING MODEL, 
CREATE ANY OPERATOR, 
CREATE ANY OUTLINE, 
CREATE ANY PROCEDURE, 
CREATE ANY SEQUENCE, 
CREATE ANY SQL PROFILE, 
CREATE ANY SYNONYM,
 CREATE ANY TABLE, 
CREATE ANY TRIGGER, 
CREATE ANY TYPE,
 CREATE ANY VIEW, 
CREATE ASSEMBLY, 
CREATE CLUSTER, 
CREATE CUBE,
 CREATE CUBE BUILD PROCESS,
 CREATE CUBE DIMENSION, 
CREATE DATABASE LINK, 
CREATE DIMENSION,
 CREATE EXTERNAL JOB,
 CREATE INDEXTYPE,
 CREATE JOB,
 CREATE LIBRARY, 
CREATE MATERIALIZED VIEW, 
CREATE MEASURE FOLDER, 
CREATE MINING MODEL,
 CREATE OPERATOR,
 CREATE PROCEDURE,
 CREATE PROFILE,
 CREATE PUBLIC DATABASE LINK, 
CREATE PUBLIC SYNONYM, 
CREATE ROLE, 
CREATE ROLLBACK SEGMENT, 
CREATE SEQUENCE, 
CREATE SESSION, 
CREATE SYNONYM, 
CREATE TABLE, 
CREATE TABLESPACE, 
CREATE TRIGGER, 
CREATE TYPE, 
CREATE USER, 
CREATE VIEW,
 DEBUG ANY PROCEDURE, 
DEBUG CONNECT SESSION, 
DELETE ANY CUBE DIMENSION, 
DELETE ANY MEASURE FOLDER, 
DELETE ANY TABLE, 
DROP ANY ASSEMBLY, 
DROP ANY CLUSTER, 
DROP ANY CONTEXT, 
DROP ANY CUBE, 
DROP ANY CUBE BUILD PROCESS, 
DROP ANY CUBE DIMENSION, 
DROP ANY DIMENSION, 
DROP ANY DIRECTORY, 
DROP ANY EDITION, 
DROP ANY INDEX, 
DROP ANY INDEXTYPE, 
DROP ANY LIBRARY,
 DROP ANY MATERIALIZED VIEW, 
DROP ANY MEASURE FOLDER, 
DROP ANY MINING MODEL, 
DROP ANY OPERATOR, 
DROP ANY OUTLINE,
 DROP ANY PROCEDURE,
 DROP ANY ROLE, 
DROP ANY SEQUENCE, 
DROP ANY SQL PROFILE, 
DROP ANY SYNONYM, 
DROP ANY TABLE,
 DROP ANY TRIGGER, 
DROP ANY TYPE,
 DROP ANY VIEW, 
DROP PROFILE, 
DROP PUBLIC DATABASE LINK, 
DROP PUBLIC SYNONYM, 
DROP ROLLBACK SEGMENT, 
DROP TABLESPACE, 
DROP USER, 
EXECUTE ANY ASSEMBLY,
 EXECUTE ANY CLASS, 
EXECUTE ANY INDEXTYPE, 
EXECUTE ANY LIBRARY,
 EXECUTE ANY OPERATOR,
 EXECUTE ANY PROCEDURE,
 EXECUTE ANY PROGRAM,
 EXECUTE ANY TYPE, 
EXECUTE ASSEMBLY,
 EXEMPT ACCESS POLICY, 
EXEMPT IDENTITY POLICY,
 EXPORT FULL DATABASE, 
FLASHBACK ANY TABLE, 
FLASHBACK ARCHIVE ADMINISTER, 
FORCE ANY TRANSACTION, 
FORCE TRANSACTION, 
GLOBAL QUERY REWRITE, 
GRANT ANY OBJECT PRIVILEGE, 
GRANT ANY PRIVILEGE, 
GRANT ANY ROLE, 
IMPORT FULL DATABASE,
 INSERT ANY CUBE DIMENSION, 
INSERT ANY MEASURE FOLDER, 
INSERT ANY TABLE, 
LOCK ANY TABLE, 
MANAGE SCHEDULER, 
MANAGE TABLESPACE, 
MERGE ANY VIEW, 
ON COMMIT REFRESH, 
QUERY REWRITE, 
RESTRICTED SESSION, 
RESUMABLE, 
SELECT ANY CUBE, 
SELECT ANY CUBE DIMENSION, 
SELECT ANY DICTIONARY, 
SELECT ANY MINING MODEL, 
SELECT ANY SEQUENCE, 
SELECT ANY TABLE, 
SELECT ANY TRANSACTION, 
UNDER ANY TABLE, 
UNDER ANY TYPE, 
UNDER ANY VIEW, 
UNLIMITED TABLESPACE, 
UPDATE ANY CUBE, 
UPDATE ANY CUBE BUILD PROCESS, 
UPDATE ANY CUBE DIMENSION, 
UPDATE ANY TABLE,
 ALTER ANY ASSEMBLY      BY <Username> BY ACCESS;



AUDIT CONNECT, DBA, RESOURCE BY <Username> BY ACCESS;

Find LAST UPDATED DDL in a TABLE

Find LAST UPDATED DDL in a TABLE

select object_name,object_type,
          to_char(created,’DD-MM-YY HH24:MI:SS’) created,
          to_char(last_ddl_time,’DD-MM-YY HH24:MI:SS’) last_ddl 
    from user_objects 
    where object_name=’ABC’;

OC4J_DBCONSOLE_localhost_orcl is not found error in OEM

OC4J_DBCONSOLE_localhost_orcl is not found error in OEM



OEM is not get connecting

If you get OC4J_DBConsole_localhost_orcl is not found


solution:

just do SET/EXPORT ORACLE_HOSTNAME and ORACLE_UNQNAME

windows:
set ORACLE_HOSTNAME=localhost
echo %ORACLE_UNQNAME%
set ORACLE_UNQNAME=ORCL
echo %ORACLE_HOSTNAME%
emctl status dbconsole
emctl stop dbconsole
emctl start dbconsole


LINUX:
export ORACLE_HOSTNAME=localhost
echo ORACLE_HOSTNAME
export ORACLE_UNQNAME=ORCL
echo ORACLE_UNQNAME
emctl status dbconsole
emctl stop dbconsole
emctl start dbconsole



it'll show you on link like "https://localhost:1158/em/console/aboutApplication"


copy that link
paste it in a browser
logon as user
ENJOY OEM in your browser

Find any table is using PRIMARY KEY of a particular table

Find any FOREIGN KEY of a table is using PRIMARY KEY of another table



select * from all_cons_columns ucc, all_constraints uc where uc.constraint_name=ucc.constraint_name and uc.constraint_type='P' and uc.table_name = 'QP_ACTIVITY';


select * from all_cons_columns ucc, all_constraints uc where uc.constraint_name=ucc.constraint_name and uc.constraint_type='P' and uc.R_CONSTRAINT_NAME = 'QP_ACTIVITY';


select * from all_cons_columns ucc, all_constraints uc where uc.constraint_name=ucc.constraint_name and uc.constraint_type='R' and uc.table_name = ucc.owner and uc .owner='VSAC';


select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name from all_constraints 
where constraint_type='R' and r_constraint_name in (select constraint_name from all_constraints where constraint_name ='IDX_QP_ACTIVITY_PK'  and table_name='QP_ACTIVITY') and owner='VSAC';

CHECK EXPORT JOB is SUCCESSFULL or NOT

CHECK EXPORT JOB is SUCCESSFULL or NOT


sqlplus / as sysdba

select * from dba_datapump_jobs;

note the jobname(SYS_EXPORT_FULL_01, SYS_EXPORT_SCHEMA_01, SYS_EXPORT_TABLE_01)


To check the KILLED,PAUSED,STOPED  JOBS

select * from dba_datapump_jobs



To check the SUCCESSFUL JOBS

select opname,target_desc,start_time,elapsed_seconds,message from v$session_longops where OPNAME='SYS_EXPORT_FULL_01' order by start_time desc;

CHECK the PRIVILEGES of a particular SCHEMA using VIEWS and SELECT

CHECK the PRIVILEGES of a particular SCHEMA using  VIEWS and SELECTALL_COL_PRIVS



ALL_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD
ALL_REPGROUP_PRIVILEGES
ALL_TAB_PRIVS
ALL_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD


USER_AQ_AGENT_PRIVS
USER_COL_PRIVS
USER_COL_PRIVS_MADE
USER_COL_PRIVS_RECD
USER_REPGROUP_PRIVILEGES



USER_ROLE_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD


NOTE:
Before running these queries you have login into the particular user

set heading off
set pages 0
set long 9999999
select dbms_metadata.get_granted_ddl('ROLE_GRANT', user)  from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', user)  from dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', user)  from dual;

Find a DDL and DML queries of particular OWNER ans TABLE

Find a DDL and DML queries of particular OWNER ans TABLE


SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner) FROM all_OBJECTS WHERE (OWNER = 'OWNER1');


SELECT DBMS_METADATA.GET_DML(object_type, object_name, owner) FROM all_OBJECTS  WHERE (OWNER = 'ASUB');

find the insert query

select 'INSERT INTO QP_BANK_INFO values(''' || Name || ','|| AGE ||',''' || Address ||''');' from QP_BANK_INFO;

 change the column names of a table

ADD a PRIVATE KEY and FOREIGN KEY to a TABLE

ADD a PRIVATE KEY and FOREIGN KEY to a TABLE

The best views to check CONSTRAINTS in a Database

ALL_CONSTRAINTS

all_cons_columns



PRIMARY KEY
************************************

Sample Script
************

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   PRIMARY KEY (column1, column2, ... column_n)
   REFERENCES parent_table (column1, column2, ... column_n);




ALTER TABLE QP_RTPN ADD CONSTRAINT PK_QP_RTPN_PARAM_ID PRIMARY KEY (ID)  REFERENCES QP_RTPN_PARAM (ID);



CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)

);

FOREIGN KEY

********************************

Sample script

************

ALTER TABLE table_name

ADD CONSTRAINT constraint_name

   FOREIGN KEY (column1, column2, ... column_n)

   REFERENCES parent_table (column1, column2, ... column_n);

ALTER TABLE QP_RTPN ADD CONSTRAINT FK_QP_RTPN_PARAM_ID FOREIGN KEY (ID)  REFERENCES QP_RTPN_PARAM (ID);

select * from all_cons_columns;

CREATE TABLE table_name

(

  column1 datatype null/not null,

  column2 datatype null/not null,

  CONSTRAINT fk_column

    FOREIGN KEY (column1, column2, ... column_n)

    REFERENCES parent_table (column1, column2, ... column_n)

);

ALTER TABLE employee ADD FOREIGN KEY (group_id) REFERENCES product_groups(ID);