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 →

Most read

Download and Install of Java (JRE) on Centos Hello Oracle Golden Gate – Super simple Golden Gate monitor shellscript awk Restore SQL Server database with TSQL Create, drop and rename a database Script to get size of generated archived logfiles in certain timeframe Remove SQL Server database from single-user mode Import / export datapump using FLASHBACK_TIME or FLASHBACK_SCN ( and NETWORK_LINK ) fails with ORA-31693, ORA-01031 Purging SYSAUX tablespace (Purging AWR reports) invalid objects after clean ctxsys installation (oracle text) Trace you own session using dbms_system / set events Removing unused columns online using DBMS_REDEFINITION Progress OpenEdge Database 10.1b install on Centos (RHEL) Fix ORA-00257: archiver error. Connect internal only, until freed – in Oracle 11G shutdown immediate taking ages (two days) to complete – or – ‘No, I will NOT enlarge the UNDO tablespace’ – or – patience … Use filters to keep your traces meaningfull Script to find duplicate datafile names return variables from dynamic sql – sp_executesql net use: map network share Centos 5.0 install fails, kernel panic init parameter plsql_code_type braindump 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. 11g installation on Linux (Centos) Create a progress database SQL Trace Reference cursors in tsql Really delete (‘corrupt’) Agent from OMS repository pipe freaking hilarious incident… DBMS_NETWORK_ACL_ADMIN.CREATE_ACL returns ORA-30992 awk remove file with filenumber less then monitor progress of long running job with dbms_application_info.set_session_longops Control Structures Increase idle time for OMS frontend sessions Batch SQL statements in parallel (multiple) sessions Segment Advisor dump (R 10.1) Oracle Advisor – DBMS_ADVISOR dbverify in nohup nohup Invalidated synonyms Generate scp datafiles script with regular expression RMAN cheatsheet SQL query to show tables vs indexes in your tablespaces sp_configure mailx ORA-01114: IO error writing block to file – returns wrong file number – can’t find file delete large number of records with intermediate commits change database id of database osql switches new explain plan options (dbms_xplan) while true do heredoc golden gate info all Date Time functions Standalone Internet Explorer 7 install yum Why Oracle May Really Be Doomed This Time MSSQL cheat sheet for the Oracle minded To trace or not to trace Configure telnet expdp / impdp Manually triggering Metric Collection (clearing -tablespace- Alerts) overcome SP2-0734 when using purge command PL/SQL quickie; generate RMAN set newname script to different mountpoints ORAENV_ASK SQL script to check available space in your recoveryarea (db_recovery_file_dest_size) find Automatic Workload Repository Add simple DDLERROR exception in Replicat to ingore certain ORA errors Transporting an Oracle database to another os platform the fastest way a winner, flashback query; AS OF TIMESTAMP Microsoft SQL Server 2005 JDBC Driver (oracle GRID) Windows handies Using Email (utl_smtp) in Oracle 11g (results in ORA-24247) – Network Access Control List (ACL). Clearing Alerts in Oracle GRID Control dba$log_blocker Make Joomla! multi sites enabled.

Categories

Contractees