Remi Visser
Oracle DBA

busy bee in IT

Hire me About & Contact LinkedIn

Latest Braindumps

awk remove file with filenumber less then

I’m restoring a large amount of oracle archived redo logs into one directory. …

The archived redolog filenames have the following names ‘1_116133_820730017.dbf’ – ‘1_{log_sequence_number}_820730017.dbf’. The log sequence number is reflected in the filename, it is the second number pair – between …

Read more →

RMAN cheatsheet

Commands
SET
LIST
RESTORE …

LIST ARCHIVELOG
Objects
archivelogRecordSpecifier …

# List archives by log sequence
LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 3398;

# List archives with NEXT_TIME (see <a href="http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_1016.htm">v$archived_log.next_time</a>) is  greater then FROM TIME - last day (24 hours)
LIST
Read more →

init parameter plsql_code_type braindump

Some code for plsql_code_type below; …

Current setting

select *
from v$parameter 
where name = 'plsql_code_type'
/

breakdown per plsql_code_type

col owner for a18
col plsql_code_type for a18


select owner, plsql_code_type, count(*)
from dba_plsql_object_settings 
group by owner, plsql_code_type
order by owner, plsql_code_type
/

compile schema(s)

Read more →

Script to find duplicate datafile names

Whenever you want to duplicate (copy/scp) a database and place all datafiles into one single directory you always have to check if there are no duplicate filenames. …

Eg like; …

/m001/oradata/oracle_sid/filename1.dbf
/m002/oradata/oracle_sid/filename1.dbf …

scp /m*/oradata/oracle_sid/*dbf remote_host:/m001/oradata/dup_oracle_sid/. …

SQL> select substr ( file_name, instr( file_name,
Read more →

Trace you own session using dbms_system / set events

For debugging … Make sure the session has a direct execute grant on dbms_system package. …

Level 12 makes sure to include the waits and bind data. dbms_system …

begin
for i in (select sid, serial# from v$session where audsid=SYS_CONTEXT('USERENV','SESSIONID'))
loop
	sys.dbms_system.set_ev ( si
Read more →

ORA-01114: IO error writing block to file – returns wrong file number – can’t find file

I was notifed by developer team that they get a whole lot of ORA-01114 errors. …

********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:2012-07-26 <!--more-->19:02:32.484306*:9C82F5B9:sql_mon_query:keswx.c@3633:keswxWriteEndInfoToStream(): done
Read more →

Overcome “ERROR: ‘${ORACLE_HOME}/agent11g’ subdirectory already exists in Oracle Inventory. Specify another home location.” when doing a silent install in a non-empty Oracle home.

While testing our silent Oracle Grid agents installs I ran into error below when (re-)installing into a non-empty Oracle home directory. …

$ ./runInstaller -silent -responseFile /home/oracle/additional_agent.rsp -invPtrLoc /var/opt/oracle/oraInst.loc
.
.
ERROR: Error:"${ORACLE_HOME}/agent11g" subdirectory already exists in Oracle Inventory. Specify another home location.
.
.

This …

Read more →

SQL query to show tables vs indexes in your tablespaces

SQL query below displays a tables / indexes breakdown of your tablespaces. …

SELECT tablespaces.tablespace_name, 
			NVL( tables, 0) as tables, 
			NVL( indexes, 0) as indexes
FROM 	dba_tablespaces tablespaces
	LEFT OUTER JOIN ( 
				SELECT COUNT(*) tables, tablespace_name 
				FROM dba_segments 
				WHERE segment_type = 'TABLE'
Read more →

Contractees