ASMCMD-08102: no connection to ASM

[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/grid
[oracle@localhost ~]$ export PATH=/u01/app/oracle/grid/bin
[oracle@localhost ~]$ export ORACLE_SID=+ASM
[oracle@localhost ~]$ asmcmd

ORA-04021: timeout occurred while waiting to lock object

U have to kill all sessions using object which u wanna compile, u can use this below script to find out which sessions are using any object..

select 'kill -9 '||b.spid||' # username '||c.username||'' from gv$access a, gv$process b, gv$session c where a.object=upper('object_name') and a.sid=c.sid and b.addr=c.paddr


run the output on linux to kill all sessions then compile object

How to check ASM HOME !!

cat /etc/oratab

create a directory for datapump

CREATE OR REPLACE DIRECTORY DP AS '/u01/app/oracle/datapump/';
GRANT READ, WRITE ON DIRECTORY DP TO system;

Extract Tablespaces with dbms_metadata.get_ddl

select 'select dbms_metadata.get_ddl(''TABLESPACE'','''
  ||  tablespace_name || ''') from dual;' from dba_tablespaces

How to list of dates between 2 dates

How to list of dates between 2 dates

select to_date('23-04-2011', 'DD-MM-YYYY') + rownum -1 dt
    from dual
    connect by level <= to_date('05-05-2011', 'DD-MM-YYYY') - to_date('23-04-2011', 'DD-MM-YYYY') + 1;

OGG-00529 DDL Replication is enabled but table OGG.GGS_DDL_HIST is not found

OGG-00529  DDL Replication is enabled but table OGG.GGS_DDL_HIST is not found

u must be in the goldengate directory..

SQL> alter system set recyclebin=off;
System altered.

SQL> @marker_setup
Marker setup script
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:OGG
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.

SQL> @ddl_setup

OGG-00512

ERROR   OGG-00512  RECYCLEBIN must be turned off. For 10gr2 and up, set RECYCLEBIN in parameter file to OFF. For 10gr1, set _RECYCLEBIN in parameter file to FALSE. Then restart database and extract.

connect with sysdba ;

alter system set recyclebin=off scope=spfile;

then restart database

SQL> startup force

SQL> show parameter recyclebin 

recyclebin                           string      OFF

how to Kill scheduler job

exec dbms_scheduler.stop_job('JOB')

Restore database with rman -- 'exclude tablespace' and fixing RMAN-06054

if u take rman backup with exclude tablespace and restore database with this script 

run {
set until time "to_date('2012-02-28 00:00:00','YYYY-MM-DD HH24:MI:SS')";
restore database;
recover database;
alter database open resetlogs;
}



u will get this error;

RMAN-06054: media recovery requesting unknown log:

u need to perform offline drop for excluded tablespace;

first of all;

rman> alter database open resetlogs;

ORA-01157: cannot identify/lock data file 39 - see DBWR trace file
ORA-01110: data file 39:

as u see data file 39 need to be droped ;

so just drop it offline;

RMAN> sql 'alter database datafile 39 offline drop';

sql statement: alter database datafile 39 offline drop

RMAN> alter database open resetlogs;

database opened


thats it..