Sunday, December 11, 2016

Google Cloud Platform Fundamentals in Sydney

Just finished up one day training at Google's Sydney office in Google Cloud Platform Fundamentals. GCP is pretty cool and I think I like it.

Lots of our customers at Pythian are already hosting, migrating and thinking of doing so on cloud. Pythian already has a huge presence in cloud using various technologies.

So it was good to learn something about the Google's cloud offering. It was a pleasant surprise as it all made sense. From App engine to compute engine and from big table to big query, the features are sound, mature and ready to use.

The dashboard is simple too. I will be blogging more about it as I play with it in coming days.

Sunday, October 23, 2016

Speaking at APAC OTN TOUR 2016 in Wellington, New Zealand

The APAC OTN Tour 2016 will be running from October 26th until November 11th visiting 4 countries/7 Cities in the Asia Pacific Region.

I will be speaking at APAC OTN TOUR 2016 in Wellington, New Zealand on 26th October on the topic which is very near and dear to me; Exadata and Cloud.

My session is 12c Multi-Tenancy and Exadata IORM: An Ideal Cloud Based Resource Management with Fahd Mirza

Hope to see you there !

Saturday, October 8, 2016

ORA-01156 When Adding Standby Redo Log in Dataguard Configuration

Database version =

If you get following error while adding a Standby Redo Log (SRL) to a standby database in dataguard configuration:

SQL> alter database add standby logfile group 3 '/logs/test/sbyredo3a.log' size 500M;
alter database add standby logfile group 3 '/logs/test/sbyredo3a.log' size 500M
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

Then it means that first you have to stop the redo apply, add the SRL and then start the redo apply. Best way to do is from dgmgrl like this:

DGMGRL> connect /

DGMGRL> edit database 'test' set state='APPLY-OFF';

SQL> alter database add standby logfile group 3 '/logs/test/sbyredo3a.log' size 500M;

Database altered.

DGMGRL> edit database 'CONPRO_UK' set state='APPLY-ON';


Saturday, July 30, 2016

Wordpress Fatal error: Call to undefined method PHPMailer::getCustomHeaders()

With numerous new themes and new versions of the wordpress, when you try to publish some post or page or try to send email or try to use contact form, you might get following error:

Fatal error: Call to undefined method PHPMailer::getCustomHeaders()

Wednesday, February 10, 2016

Opatchauto Session failed: Parameter validation failed

While applying PSU on Grid Home in 12c, due to the patch conflict, you might have to rollback few patches before you could apply the PSU.

After rolling back the patches from grid home, when you try to run the opatch analyze command again, you might encounter following error:

Tuesday, February 9, 2016

Step by Step Jan 2016 PSU Patch Apply on 12c Grid and RDBMS Homes in Linux

Following step by step action plan is for single instance database stored on ASM in on Linux (OEL 6 64 bit in this case.)

Update the OPATCH utility:

For Database home:

$ unzip -d /u01/app/oracle/product/12.1.0/db_1
$ /u01/app/oracle/product/12.1.0/db_1/OPatch/opatch version

For Grid home:

$ unzip -d /u01/app/oracle/
$ /u01/app/oracle/ version
15 min
Create ocm.rsp file:

Note: Press Enter/Return key and don't provide any input and say Yes.

$ export ORACLE_HOME=/u01/app/oracle/
$ $ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /stage/ocm.rsp
5 min
Validation of Oracle Inventory

Before beginning patch application, check the consistency of inventory information for GI home and each database home to be patched. Run the following command as respective Oracle home owner to check the consistency.

For database home:

$ /u01/app/oracle/product/12.1.0/db_1/OPatch/opatch lsinventory -detail -oh /u01/app/oracle/product/12.1.0/db_1

For Grid home:

$ /u01/app/oracle/ lsinventory -detail -oh /u01/app/oracle/

If this command succeeds, it lists the Oracle components that are installed in the home. Save the output so you have the status prior to the patch apply.
5 min
Stage the Patch:

$ mkdir /stage/PSUpatch
$ cp /stage/ /stage/PSUpatch

Check that the directory is empty.
$ cd /stage/PSUpatch
$ ls

Unzip the patch as grid home owner.

$ unzip
5 min
One-off Patch Conflict Detection and Resolution:

Run it with root user:

/u01/app/oracle/ apply /stage/PSUpatch/22191349 -analyze -ocmrf /stage/ocm.rsp

It will ask to rollback identical patches like this:

Analyzing patch(es) on "/u01/app/oracle/" ...
Patch "/stage/PSUpatch/22191349/21436941" is already installed on "/u01/app/oracle/". Please rollback the existing identical patch first.
Patch "/stage/PSUpatch/22191349/21948341" is already installed on "/u01/app/oracle/". Please rollback the existing identical patch first.
Patch "/stage/PSUpatch/22191349/21948344" is already installed on "/u01/app/oracle/". Please rollback the existing identical patch first.
Patch "/stage/PSUpatch/22191349/21948354" is already installed on "/u01/app/oracle/". Please rollback the existing identical patch first.

So first rollback above 4 patches by going to their directory and issuing with grid owner from grid home:

opatch rollback -id 21948354 -local -oh /u01/app/oracle/ (Repeat for all 4 patches)

Note: In some cases, weirdly, I had to shutdown the has services with root user before patch rollback by using:

/u01/app/oracle/ stop has -f

After this again run:

/u01/app/oracle/ apply /stage/PSUpatch/22191349 -analyze -ocmrf /stage/ocm.rsp

If analyze command fail then use this with root user:

$ORA_GI_HOME/crs/install/ –postpatch

It will start the has services too.

Then again run the analyze command as given above:

It will show something like:

Analyzing patch(es) on "/u01/app/oracle/" ...
Patch "/stage/PSUpatch/22191349/21436941" successfully analyzed on "/u01/app/oracle/" for apply.
Patch "/stage/PSUpatch/22191349/21948341" successfully analyzed on "/u01/app/oracle/" for apply.
Patch "/stage/PSUpatch/22191349/21948344" successfully analyzed on "/u01/app/oracle/" for apply.
Patch "/stage/PSUpatch/22191349/21948354" successfully analyzed on "/u01/app/oracle/" for apply.

Now you are good to apply the patch. Proceed to next step.

10 min
Apply the Patch: (Note: This should apply patch in both GI and RDBMS Home but its unreliable in that sense so after this completes, we need to check opatch lsinventory to make sure that it also applied patches in RDBMS Home)

As root user, execute the following command:

# /u01/app/oracle/ apply /stage/PSUpatch/22191349 -ocmrf /stage/ocm.rsp

In case if it doesn’t apply in RDBMS Home, then run:

/u01/app/oracle/product/12.1.0/db_1/OPatch/opatchauto apply /stage/PSUpatch/22191349 -oh /u01/app/oracle/product/12.1.0/db_1 -ocmrf /stage/ocm.rsp

Make sure the above applies both OCW and PSU patches. You can verify that from opatch lsinventory. If only OCW patch is present in output and no PSU (which is likely the case), then issue following from Oracle home with oracle database owner after shutting down database:

/u01/app/oracle/product/12.1.0/db_1/OPatch/opatch apply -oh /u01/app/oracle/product/12.1.0/db_1 -local /stage/PSUpatch/22191349/21948354
60 min
Loading Modified SQL Files into the Database:

% sqlplus /nolog
SQL> Connect / as sysdba
SQL> startup
SQL> quit
% cd $ORACLE_HOME/OPatch
% ./datapatch -verbose
60 min
Check for the list of patches applied to the database.

SQL> select action_time, patch_id, patch_uid, version, status, bundle_series, description from dba_registry_sqlpatch;
5 min

Friday, October 16, 2015


There might be a situation where executing some DDL in pluggable database may cause the following error:

ORA-65040: operation not allowed from within a pluggable database

This error could occur if a tablespace is being dropped from within PDB and this tablespace is a former default tablespace having some of the system objects. Even system objects cannot be moved with simple alter statements from within PDBs.

So in order to move these objects from within PDBs, you should be using procedure dbms_pdb.exec_as_oracle_script which is undocumented so far.

For example:

exec dbms_pdb.exec_as_oracle_script('alter table . move tablespace ');

From My Oracle Support, Doc ID 1943303.1 lists:

--   This procedure enables execution of certain restricted statements (most DDLs) on metadata-linked objects, from within a PDB.

Monday, October 12, 2015

Got Published in AUSOUG's Foresight Online Spring 2015

AUSOUG's Foresight Online Spring 2015 Edition is the premier publication by Australian Oracle User Group.

Following are highlights of this edition:

  • President's Message
  • DBA Article: Automated Testing of Oracle BPM Suite 12c Processes with SOAP UI - Peter Kemp, State Revenue Office, Victoria
  • DBA Article: Best Practices for Oracle on Pure Storage
  • Apps Article: Performance Review Data Capture - Brad Sayer, More4Apps
  • DBA / Dev Article: Database Developers – Feeling Left Out of Agile? - D Nowrood, Dell Software
  • Apps Article:  Cost-effective alternative to Oracle Discoverer and BI Suite - Wilhelm Hamman, Excel4apps
  • DBA Article: DBA101 - characterset corruption - Paul Guerin, HP
  • Quick Tips 1: Five Reasons to Upgrade to APEX 5.0 - Scott Wesley, Sage Computing Services
  • Quick Tips 2: Last Successful login time in 12c - Fahd Mirza Chughtai, The Pythian Group

Sharding in Oracle 12c Database

Sharding for Oracle DBAs is still pretty much an alien or pretty new concept. In the realms of big data, this term is being used quite extensively though.

What is Sharding in simple words:

Sharding is partitioning. Horizontal partitioning to be exact.

Sharding means partitioning a table rows on basis of some criteria and storing that partitioned rows of table (i.e. a shard) on different database servers. These database servers are cheap low commodity servers.

The benefits include smaller data to manage, smaller backups, faster reads, and faster response time for the queries.

Just like existing partitioning option in the Oracle database, there are generally three kinds of sharding:

Range Sharding
List Sharding
Hash Sharding

The news out there on social media is that Oracle 12c next version is coming up with Sharding option. That is pretty exciting and let's see what they come up in this regard.

Friday, September 18, 2015

ORA-01917: user or role 'PDB_DBA' does not exist

I manually created a container database (CDB) in my Linux 6.4 based virutal machine. After creating it, I tried to create a pluggable database but got following error: