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

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

Categories

Contractees