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…

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 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…

Transporting an Oracle database to another os platform the fastest way

You can use RMAN to transport tablespaces across platforms with different endian formats. You can also use RMAN to transport an entire database to a different platform so long as the two platforms have the same endian format. — From “27 Transporting Data Across Platforms” – http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmxplat.htm So, in my own words – datafiles (tablespaces) […]

Read More…

Fix ORA-00257: archiver error. Connect internal only, until freed – in Oracle 11G

When your database hangs with a ORA-00257 error while you notice there is plenty of space in the /recoveryarea mountpoint you’re likely to have reached the maximum size defined by the new ** initialization parameter ‘db_recovery_file_dest_size‘. Fast solution is to simply increase the value for db_recovery_file_dest_size (and after that of course start archiving…) ** 2012-04-02T12:10:; […]

Read More…

PL/SQL quickie; generate RMAN set newname script to different mountpoints

Script below generates RMAN newname file where database A is copied to database B AND database B has ‘conflicting’ mountpoints set up. Mountpoints setup on database A is like /m001/, /m002/, /m003/, /m004/ but mountpoints on database B side is just /m001/ and /m002. /m001 has only 800 G free space so I place everything […]

Read More…