Database Technologies...!

This is a result of silly mistake where legacy exp syntax is used with expdp. The issue here is when expdp with executed with exp parameters it runs with expdp conversion where applicable or ignore the legacy syntax. In this particular scenario expdp was called with  i ndexes=n compress=y parameter for table export. Now the expdp has converted indexes=n to  exclude=index but has ignored compress=y , most probably since expdp compression has three options (none, meta_data, all). This is recorded in expdp log as below. Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "compress=TRUE" Location: Command Line, ignored. Legacy Mode Parameter: "indexes=FALSE" Location: Command Line, Replaced with: "exclude=index" Legacy Mode has set reuse_dumpfiles=true parameter. Starting "DBA00000"."SYS_EXPORT_TABLE_01": Although this situation is due to a syntax error, it is not clear at a first glance since w

Although it not very common to manually change the AWS RDS availability zone we have come across two situation where it was required. 1. Application was using RDS IP, during a AWS maintenance windows RDS was moved to secondary availability zone. Quick code change was not an option so we had to manually switch to original availability zone 2. After a reboot of the RDS instance there were some I/O performance degradation was monitored. We thought it might be due to host system change on AWS and decided to do a manual switch to passive availability zone. There we haven't seen I/O issue. It is straight forward to do a manual availability zone switch using the console. Go to, Instance actions -- Reboot -- Make sure to tick the "Reboot With Failover"

Apparently alter system kill session is failing with ORA-01031: insufficient privileges. In Oracle RDS even if you are using privileged account ( rdsadmin ) you still cannot use the default syntax. Instead we need to execute  rdsadmin.rdsadmin_util.kill procedure. Example below. SQL> alter system kill session '1261,5589' immediate; alter system kill session '1261,5589' immediate * ERROR at line 1: ORA-01031: insufficient privileges SQL> ------------------------------- SQL> begin 2 rdsadmin.rdsadmin_util.kill( 3 sid => 1261, 4 serial => 5589, 5 method => 'IMMEDIATE'); 6 end; 7 / PL/SQL procedure successfully completed. SQL>

DB Version - Cloud control 12c Release 5 Setting up alerts is a must for any monitoring tool and when it comes to Oracle cloud control email is the only straight forward configuration. There is a possibility to configure sms by using mail to sms service but for most shops this is bit unpractical. Mainly due to security reasons to use a public service (e.g Google) to convert email to sms. But most shops does have their own sms gateways where they get data from a database or files and sent it via sms. In our case we did have a sms gateway so the challenge was to forward sms alerts to sms gateway source database tables. Below is the basic configuration setup. All the alerts generated by cloud control are stored in the cloud control database. What we need to do is transfer those alert data from cloud control db to sms gateway db. Some digging in the cloud control documentation and googling helped us to come up with below steps to accomplish this. In summary there will be

We can use dba_source and  dbms_metadata.get_ddl when extracting code from db objects. Something that wasn't clear was code for scheduler jobs. Below is the syntax for extracting scheduler job code using dbms_metadata.get_ddl. select dbms_metadata.get_ddl('PROCOBJ','-SCH_JOB_NAME-','-OWNER-') from dual; Not sure where they came up with PROCOBJ. :D

DB Version - Cloud control 12c Release 5 Ok, this seems straight forward enough, but for me it took some digging to find the proper method. These report are already built in to cloud control 12c. report names are as follows and they provide all the required information on tablespace growth. Report Path -- Enterprise > Information Publisher Reports > Oracle Database Space Usage Under this section you can find both Oracle Database Tablespace Daily Space Usage and Oracle Database Tablespace Monthly Space Usage reports.

Ever come across a situation where you need to perform data pump operation on object owned by SYS?. This is not allowed in data pump and your operation should fail with ORA-39166.The old exp/imp was able to perform operation on SYS owned objects. Ideally you should not be in an situation to data pump on SYS. but in our case it was a data migration from Oracle 8i to 11g, and there was an intermediate state where some data modification to be performed. So when importing 8i data using old imp it was by mistake moved to SYS. And these were some huge tables with some data modification already done. We had the option of using old imp/exp but was preferring parallel data pump due to the performance. So what are options to quickly change ownership SYS owned table without recreating the tables. Enter - Partition exchange for rescue. This method is a quick workaround to change ownership of Oracle table without time consuming recreates. Below is an example where we change table owner ship