Remi Visser Oracle DBA

Hi, I am an Oracle DBA with almost 15 years experience. Here is where I store some of my braindumps. You can contact me using LinkedIn or GMAIL.

Employers

remivisser@gmail.com LinkedIn

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 the first and the second underscore. So it is like: 1_116133_820730017.dbf is 1_<log_sequence_number>_820730017.dbf # […]

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 BACKUP OF ARCHIVELOG FROM TIME "SYSDATE-1"; # List archives between two dates LIST BACKUP OF […]

Read More…

Add simple DDLERROR exception in Replicat to ingore certain ORA errors

Short braindump about adding a DDLERROR error exception in your Golden Gate REPLICAT configuration. In this scenario the goal is to prevent my replicat from abending over an ‘ORA-01432: public synonym to be dropped does not exist ‘ error. Check statuses for all OGG processes GGSCI () 3> info all Program Status Group Lag at […]

Read More…

Script to get size of generated archived logfiles in certain timeframe

I’m testing a import datapump load procedure and I need to know the approximate size of generated archives that this load will generate to determine the size needed for our database recoveryarea. Script below queries the v$archived_log view for this information. Please note that I have tested this with ONE and only ONE active / […]

Read More…

Import / export datapump using FLASHBACK_TIME or FLASHBACK_SCN ( and NETWORK_LINK ) fails with ORA-31693, ORA-01031

I’m writing a shellscript that loads a remote schema into my database using impdp network_link – all goes well. Only once I add the FLASHBACK_TIME I got error below; ORA-31693: Table data object "<TABLE_OWNER>"."<TABLE_NAME>" failed to load/unload and is being skipped due to error: ORA-01031: insufficient privileges ORA-02063: preceding line from <DATABASE_NAME> Here is my […]

Read More…

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL returns ORA-30992

The problem – issueing CREATE_ACL for a new ACL list results in ORA-30992. SQL> BEGIN 2 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( 3 acl => ‘acl_********.xml’, 4 description => ‘Email ACL (*.*.nl + *.*.nl)’, 5 principal => ‘SYSTEM’, 6 is_grant => TRUE, 7 privilege => ‘connect’, 8 start_date => SYSTIMESTAMP, 9 end_date => NULL); 10 COMMIT; 11 end; 12 […]

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) After changing init.ora paramter begin for i in ( select * […]

Read More…

Using Email (utl_smtp) in Oracle 11g (results in ORA-24247) – Network Access Control List (ACL).

Using UTL_SMTP to send email from your oracle database has changed in Oracle 11g. Or – to be more precise – accessing the remote network has changed. Initializing a remote connection using UTL_SMTP will typically result in error below if you did not configure your database network access lists (ACL) for the user involved. -1. […]

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, ‘/’, -1)) file_name, count(*) 2 from dba_data_files 3 group by substr ( file_name,instr( file_name, […]

Read More…

shutdown immediate taking ages (two days) to complete – or – ‘No, I will NOT enlarge the UNDO tablespace’ – or – patience …

Third party asked for a restart of one of our testing databases that was having issues. Although I’m always a bit skeptical on simple restart requests I think in this case it actually turned out to shed some light; one huge transaction of a ‘runaway process’ turned out to virtually block all other operations … […]

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 => i.sid, se => i.serial#, ev => 10046, LE => 12, nm => […]

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 error can easily be overcome by manually editting the ‘inventory.xml […]

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’ GROUP BY tablespace_name ) tables ON tablespaces.tablespace_name = tables.tablespace_name LEFT OUTER JOIN ( SELECT […]

Read More…

SQL script to check available space in your recoveryarea (db_recovery_file_dest_size)

We’re hitting a lot of ORA-19815, ORA-19809, ORA-00257, ORA-16038 these days on some of our development databases – see here. Scroll down for an updated version. The space_reclaimable should be substracted from space_used. Script below displays percentage used in in your recoveryarea(s) as defined in init parameter ‘db_recovery_file_dest_size’. col name for a32 col size_m for […]

Read More…


Feedback



Purnendu said:
good one

Gio said:
Fixed Thank you !


ClarkX said:
Excellent!


Senthil Murugan said:
good fix, thanks


sanjaya said:
Fixed Thank you !

Ramesh Velauthem said:
Timely helped...






Maulik said:
Thanks :)





Shailesh Govani said:
Very useful script

chandu said:
thank you very much.



remivisser said:
Thanks Laurent :)

Tag & Categories

awk convert installer oraInst.loc rman shutdown undo *nix 10g 11g 12c Administration DBA util Grid Joomla! Misc mssql mssql for the oracle minded New Features oracle Oracle Golden Gate os commands performance and tuning Progress scripts SQL Trace T-SQL Uncategorized vmware windows