ORA-600 [ktu_format_nr no disk align] - Oracle 12c Database Migration stuck at Convert Datafile of UNDO tablespace

Bugs Bugs Bugs... 3rd bug this month!

So, I was performing a simple cross-platform Database Migration from "Solaris[tm] OE (64-bit)" to "HP-UX IA (64-bit)". As both the source and the target OS runs on the BIG endian format, the migration using "RMAN transportable database" is the simplest - requiring to convert only the datafiles/tablespaces containing some kind of UNDO segment.

Steps for this migration can be found at:
Cross-Platform Database Migration (across same endian) using RMAN Transportable Database (Doc ID 1401921.1)

Now, I had 1 file from SYSTEM tablespace and a few from UNDOTBS_01 to convert.
Most of them converted without a hickup, but one file "undotbs_01_01.dbf" would get stuck while conversion.

RMAN> CONVERT 
DATAFILE '/ofdmdb/STAGE_DIR/undotbs_01_01.dbf' 
FORMAT '/ofdmdb/undotbs_01_01.dbf' 
FROM PLATFORM 'Solaris[tm] OE (64-bit)' ; 

Starting conversion at target at 18-JAN-17 
using channel ORA_DISK_1 
channel ORA_DISK_1: starting datafile conversion 
input file name=/ofdmdb/STAGE_DIR/undotbs_01_01.dbf 

--- -- - Stuck here since 2 hours and counting... 

I check the alert.log and found the below:

Thu Jan 19 15:06:46 2017 
Errors in file /u01/app/oracle/diag/rdbms/ofdm/ofdm/trace/ofdm_ora_16742.trc (incident=65019): 
----->>> ORA-00600: internal error code, arguments: [ktu_format_nr no disk align], [104], [33281], [45056], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/ofdm/ofdm/incident/incdir_65019/ofdm_ora_16742_i65019.trc 
Thu Jan 19 15:06:49 2017 
Dumping diagnostic data in directory=[cdmp_20170119150649], requested by (instance=1, osid=9513668), summary=[incident=65019]. 
Use ADRCI or Support Workbench to package the incident. 
See Note 411.1 at My Oracle Support for error and packaging details. 
Thu Jan 19 15:07:08 2017 
Sweep [inc][65019]: completed 
Sweep [inc2][65019]: completed 

On searching for related documents I found that this was a Bug and there were no straight patches available yet:

Bug 24332831 - RMAN: ORA-600 [ktu_format_nr no disk align] while convert from HP-UX ( Doc ID 24332831.8 ) 
or a more accessible post:
http://remidian.com/2012/07/transporting-an-oracle-database-to-another-os-platform-the-fastest-way

I then had to raise a ticket with Oracle Support and they prepared a bugfix patch (Patch 24332831 for HP-UX) for us based on our version and patch level.

I then applied the patch using the usual "opatch apply", and then tried to convert the file again:


oracle->sysdev]/u01/software/bugfix/24332831>rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jan 23 09:06:45 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: OFDM (not mounted)

RMAN> CONVERT DATAFILE '/ofdmdb/STAGE_DIR/undotbs_01_01.dbf'
FORMAT '/ofdmdb/undotbs_01_01.dbf'
FROM PLATFORM 'Solaris[tm] OE (64-bit)' ;2> 3>

Starting conversion at target at 23-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/ofdmdb/STAGE_DIR/undotbs_01_01.dbf
converted datafile=/ofdmdb/undotbs_01_01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
Finished conversion at target at 23-JAN-17

Finally, I proceeded with the next steps in migration without another hiccup.

Oracle 12c RAC: A step by step manual PSU patching (October 2016) without using opatchauto

Why would you want to 'not' use OPATCHAUTO? Maybe because there's no bash shell in your server due to some reason, or you just want to avoid any complications by opatchauto, or simply you like a more granular control over the patching process.

I primarily work on Oracle RAC setups on HP-UX servers - where the bash shell is not installed (and cant be due to policy). You will agree, life is much easier on Linux. Therefore I must make use of the old and dependable 'opatch' utility!

About the October 2016 patch:
Patch File:   p24412235_121020_HPUX-IA64.zip
Contents:    DB PSU patch #24006101 and OCW PSU patch #23854735
The unzipped patch file path: /u01/software/psuoct2016/12cGI/24412235

About the environment:
- In the below example, I am applying the patch on a 2-node RAC in a rolling mode. I will be sharing only the tested steps and not the outputs.

- I usually have 4 putty sessions open on each node:

1. GRID_HOME environment variables set
2. RDBMS_HOME environment variables set
3. Monitoring the activity in the alert.log of the crs
4. Monitoring the activity in the alert.log of the database

- The GRID and RDBMS home owner is 'oracle' in this example.

Prerequisites: 

Make sure your have the latest OPatch in both the RDBMS_HOME and GRID_HOME. 

$ opatch version

OPatch Version: 12.2.0.1.8

OPatch succeeded.

Let's begin...

Node 1:

1. As RDBMS owner (oracle), shutdown the instance on node 1 and stop all services on the RDBMS_HOME

$ srvctl stop instance -d testdb -i testdb1

# NOTE: Make sure the shutdown database was clean by verifying the message "Instance shutdown complete" in the alert log.

$ $ORACLE_HOME/bin/srvctl stop home -o $RDBMS_HOME -s /tmp/status -n node1

2. Unlock the CRS as ‘root’ user:

export GRID_HOME=/u01/app/12.1.0/grid 

$GRID_HOME/crs/install/rootcrs.pl -prepatch

3. As Grid Infrastructure Owner (oracle), apply the OCW patch on the Grid Home:

$GRID_HOME/OPatch/opatch napply -oh $GRID_HOME -local /u01/software/psuoct2016/12cGI/24412235/23854735

4. As Grid Infrastructure Owner (oracle), apply the DB PSU patch on the Grid Home:
$GRID_HOME/OPatch/opatch napply -oh $GRID_HOME -local /u01/software/psuoct2016/12cGI/24412235/24006101

5. As RDBMS Owner (oracle), apply the OCW PSU patch on the RDBMS Home:
$ cd /u01/software/psuoct2016/12cGI/24412235/23854735/custom/scripts
$ ./prepatch.sh
$ $ORACLE_HOME/OPatch/opatch napply -oh $RDBMS_HOME -local /u01/software/psuoct2016/12cGI/24412235/23854735

6. As RDBMS Owner (oracle), apply the DB PSU patch on the RDBMS_HOME and run ‘postpatch’:
$ORACLE_HOME/OPatch/opatch napply -oh $RDBMS_HOME -local /u01/software/psuoct2016/12cGI/24412235/24006101
cd /u01/software/psuoct2016/12cGI/24412235/23854735/custom/scripts 
./postpatch.sh

7. As root:
/u01/app/12.1.0/grid/rdbms/install/rootadd_rdbms.sh
$GRID_HOME/crs/install/rootcrs.pl -postpatch

Executing the final command "rootcrs.pl -postpatch" with lock the crs files and start the cluster services

Wait for 2-3 minutes – till all CRS components are started and DB is in OPEN state
/u01/app/12.1.0/grid/bin/crsctl stat res -t

8. As ‘oracle’:  
$ORACLE_HOME/bin/srvctl start home -o $RDBMS_HOME -s /tmp/status1 -n node1
  (No Output)

9. As ‘oracle’:
ps -ef | grep pmon 

10. Verify the Patches on both GI and RDBMS HOMES:
$ORACLE_HOME/OPatch/opatch lsinventory -oh $RDBMS_HOME
$ORACLE_HOME/OPatch/opatch lsinventory -oh $RDBMS_HOME | grep 24007012
$ORACLE_HOME/OPatch/opatch lsinventory -oh $RDBMS_HOME | grep 23854735
$ORACLE_HOME/OPatch/opatch lsinventory -oh $GRID_HOME
$ORACLE_HOME/OPatch/opatch lsinventory -oh $GRID_HOME | grep 24007012
$ORACLE_HOME/OPatch/opatch lsinventory -oh $GRID_HOME | grep 23854735

11. Check the status of CRS components:
$ crs_stat -t 

Node 2:

1. As RDBMS owner (oracle), shutdown the instance on node 1 and stop all services on the RDBMS_HOME

$ srvctl stop instance -d testdb -i testdb2

# NOTE: Make sure the shutdown database was clean by verifying the message "Instance shutdown complete" in the alert log.

$ $ORACLE_HOME/bin/srvctl stop home -o $RDBMS_HOME -s /tmp/status -n node2

Follow steps 2-7 from the Node 1 section

8. As ‘oracle’:  
$ORACLE_HOME/bin/srvctl start home -o $RDBMS_HOME -s /tmp/status1 -n node2
  (No Output)

9. As ‘oracle’:
ps -ef | grep pmon 

10. Verify the Patches on both GI and RDBMS HOMES:
$ORACLE_HOME/OPatch/opatch lsinventory -oh $RDBMS_HOME
$ORACLE_HOME/OPatch/opatch lsinventory -oh $RDBMS_HOME | grep 24007012
$ORACLE_HOME/OPatch/opatch lsinventory -oh $RDBMS_HOME | grep 23854735
$ORACLE_HOME/OPatch/opatch lsinventory -oh $GRID_HOME
$ORACLE_HOME/OPatch/opatch lsinventory -oh $GRID_HOME | grep 24007012
$ORACLE_HOME/OPatch/opatch lsinventory -oh $GRID_HOME | grep 23854735

11. Check the status of CRS components:
$ crs_stat -t 

Post -Patch Instructions:

On Node 1:
cd $RDBMS_HOME/OPatch
./datapatch -verbose


Hope this has helped you in some way...
If yes, please don't forget to add a comment!

Oracle RAC 12c - Simple steps to manually rollback a PSU Patch from a node

I will use the latest October 2016 PSU patch as a test case for this post...

Environment:
Server/OS - HP UX Itanium (11.31)
2 Node RAC - no ACFS, no shared homes, ASM is used

Method of applying patches -
Fully Manual
Non-Rolling
Why? 
 - better control,
 - non-availability of bash in  our HP UX servers which is required to run opatchauto

What do should know about the October PSU patch for RAC?
OCW PSU patch number  -- 23854735
DB PSU patch number      -- 24006101

Below are very very simple steps to rollback the patches in your environment:

DB HOME:
opatch nrollback -local -id 24006101 -oh $ORACLE_HOME
cd /u01/software/psuoct2016/12cGI/24412235/23854735/custom/scripts
./prepatch.sh
opatch nrollback -local -id 23854735 -oh $ORACLE_HOME
./postpatch.sh

GRID HOME:
$GRID_HOME/crs/install/rootcrs.pl -prepatch [-nonrolling]
opatch nrollback -local -id 24006101 -oh $GRID_HOME
opatch nrollback -local -id 23854735 -oh $GRID_HOME
$GRID_HOME/crs/install/rootcrs.pl -postpatch  [-nonrolling]

Use -nonrolling only if you have patched using the nonrolling mode.

Hope this helps!
Cheers

EM 13c: ORA-06553: PLS-306: wrong number or types of arguments in call to 'REPORT_SQL_MONITOR_LIST'

[UPDATE: Bugfix Bundle Patch is out: OMS Patch 25197714 ]


Enterprise Manager 13c Installation... Check!
Agents pushed to target servers... Check!
Cluster and Database targets discovered and configured... Check!

Aaah! EM 13c looks and feels brilliant... 

Starting to carry out the usual daily health checks on the core-banking database...
Performance page looks great...
No Locking/Blocking either...
Heading on to the SQL Monitoring page.... WHAAAAT ??? 
ORA-06553: PLS-306: wrong number or types of arguments in call to 'REPORT_SQL_MONITOR_LIST'


After a successful deployment of Oracle Enterprise Manager 13.2, I've encountered a serious BUG in the 1st hour of my EM 13c experience! 

DBAs usually depend on the "SQL Monitoring" page of the OEM to communicate the long running active queries to the App Support Teams and take necessary actions. And, the graphs always make a better impact...

So naturally, on searching for a solution on Oracle Support, I came across the below document:

EM13c: Accessing SQL Monitoring Raises ORA-06553: PLS-306: wrong number or types of arguments in call to 'REPORT_SQL_MONITOR_LIST' (Doc ID 2199723.1)

Apparently, if you are accessing a database with a lower version that 12c, you are most likely to hit this bug!
I then raised a ticket with the support, and the assigned engineer informed me that the fix is expected in the next bundle patch releasing anytime between last week of Nov and 1st week of Dec 2016. 

Stay tuned for more...

EM 13c: What happens when you don't run root.sh after the Agent installation?

Admit it, there are times when you skip execution of the little "root.sh" script, especially when you don't have direct or indirect root access. Also, you may skip it when it was already executed for a previous installation on the same server.

Obviously, root.sh is "the most" important part of a RAC setup as it configures the CRS services and brings them up, but why in OEM 13c? Hmmm...

As a DBA, it's always a best practice to check what's different, or what's new in the root.sh files when installing a newer version of any Oracle based software. That's what I did when I implemented the OEM 13c's Oracle Management Server (OMS) and deployed agents across numerous Unix, Solaris and Windows servers that were hosting Oracle Databases.

We all know that the Enterprise Manager is no longer restricted to just monitoring databases. Especially from 12c, the Enterprise Manager has been Cloudified (oooo). It can monitor and manage almost any infrastructure hardware or software (if configured). It's a complete ENTERPRISE Cloud Management Solution! Why do I say this now? Wait for it...

So, after pushing Agents on the numerous servers, I skipped executing the root.sh, as I wanted to find out if there were any impact on any of the proceeding steps.

Everything was smooth, I discovered cluster and database targets, and added them to the EM. I was able to login into these targets and perform tasks as normal. I left for home, watched the Walking Dead, and when I returned in the morning, I noticed something interesting:

The Database Targets were stuck in "PENDING STATE"!

Initially, I thought maybe a network issue causing the Upload to pause. I then did the usual things to try and fix that problem:

/u01/app/oracle/agent13c/agent_inst/bin/emctl stop agent
/u01/app/oracle/agent13c/agent_inst/bin/emctl clearstate agent
/u01/app/oracle/agent13c/agent_inst/bin/emctl start agent
/u01/app/oracle/agent13c/agent_inst/bin/emctl upload agent
/u01/app/oracle/agent13c/agent_inst/bin/emctl status agent

But it didn't work. I put on my Oracle Support socks and Google hat, and started searching for an answer. Looked at similar problems and solutions provided by my favorite Oracle Blogs - Pythian and DBAKevlar

Nada! didn't solve my problem.

Last resort - I applied the root.sh on one of the servers, and voola! In a few minutes the database targets of that server started showing Up status!

Ohhh ! I always wanted to know what happens if I don't run the root.sh!

Later, I checked if I can get the same information from somewhere else. So I headed to on of the Agent's page and guess what I found?

ERROR: NMO not setuid-root (Unix-only). What does that mean? And why does it require root.sh to be executed?

NMO is an executable file in the sbin folder of the Agent's Home. Some of the executables in this folder need to be owned by root in order for the "Enterprise" manager to be able to work for the whole "Enterprise". Clearly, the "oracle" user ownership and permissions are nearly not enough.

Before root.sh:

oracle@xxxxx /u01/app/oracle/agent13c/agent_13.2.0.0.0/sbin> ls -l
total 68594
-rwx--x--x   1 oracle   dba        22840 Sep 30 23:29 nmb.0
-rwx--x--x   1 oracle   dba       114768 Sep 30 23:29 nmgsshe.0
-rwx--x--x   1 oracle   dba       100528 Sep 30 23:31 nmhs.0
-rwx--x--x   1 oracle   dba      8695880 Sep 30 23:29 nmo.0
-rwx--x--x   1 oracle   dba      8608224 Sep 30 23:29 nmoconf
-rwx--x--x   1 oracle   dba      8617024 Sep 30 23:31 nmopdpx.0
-rwx--x--x   1 oracle   dba      8617024 Sep 30 23:31 nmosudo.0
-rwx------   1 oracle   dba        87776 Sep 30 23:31 nmr.0
-rw-r-----   1 oracle   dba         9615 Aug  1 15:37 nmr_macro_list
-rwx------   1 oracle   dba        14976 Sep 30 23:31 nmrconf

After root.sh:

oracle@xxxxx /u01/app/oracle/agent13c/agent_13.2.0.0.0/sbin> ls -l
total 203890
-rwsr-x---   1 root       dba          87224 Nov 14 10:30 nmb
-rwx--x--x   1 oracle     dba          87224 Oct  1 06:53 nmb.0
-rwxr-xr-x   1 root       dba          78656 Nov 14 10:30 nmgsshe
-rwx--x--x   1 oracle     dba          78656 Oct  1 06:53 nmgsshe.0
-rwsr-x---   1 root       dba          99680 Nov 14 10:30 nmhs
-rwx--x--x   1 oracle     dba          99680 Oct  1 06:59 nmhs.0
-rwsr-x---   1 root       dba        13002232 Nov 14 10:30 nmo
-rwx--x--x   1 oracle     dba        13002232 Oct  1 06:53 nmo.0
-rwx------   1 root       sys        13002232 Nov 14 10:30 nmo.new.bak
-rw-r-----   1 root       dba            188 Nov 14 10:30 nmo_public_key.txt
-rwx--x--x   1 oracle     dba        12857336 Oct  1 06:53 nmoconf
-rwxr-xr-x   1 root       dba        12862728 Nov 14 10:30 nmopdpx
-rwx--x--x   1 oracle     dba        12862728 Oct  1 06:59 nmopdpx.0
-rwxr-xr-x   1 root       dba        12862728 Nov 14 10:30 nmosudo
-rwx--x--x   1 oracle     dba        12862728 Oct  1 06:59 nmosudo.0
-rwsr-x---   1 root       dba         148312 Nov 14 10:30 nmr
-rwx------   1 oracle     dba         148312 Oct  1 06:59 nmr.0
-rwx------   1 root       sys         148312 Nov 14 10:30 nmr.new.bak
-rw-r-----   1 root       dba           9615 Aug  1 22:37 nmr_macro_list
-rwx------   1 oracle     dba          80648 Oct  1 06:59 nmrconf

I hope you see the difference in permissions and ownership of the files.

Once I applied the root.sh on the remaining servers, all the cluster and database targets were up and running.

Let me know if you have any questions or a conflict of thoughts :) !

Cheers,

Getting started in Oracle Cloud - Database as a Service

Cloud - the recent talk of the tech-town. We all know Oracle has fired up the sales teams all over the world across all spheres of Businesses with a single goal - convince the customer to move to Cloud now !

Well, as technical beasts - we are more interested in how we can transition into this wave of Cloud computing, especially if you are an Oracle DBA.

Oracle is currently giving you 1 month free trial of the Cloud Services - so please make hay while the sun shines (that's lame :P)... 

To begin with, register HERE

Under Platform --> Data --> Database --> Try it --> Database as a Service --> Star Trial

P.S. - I had some difficulty to register myself as the Request Code that is sent through SMS didn't arrive at all. For any difficulty - please click the "Chat" icon to get immediate support from Oracle. They are pretty helpful.

You will receive an email with the subject "Oracle Cloud Access Details" which will give you direct access links to the dashboard.

A great place to start with following the instructions here - DBaaS Quick Start Guide

This is a simple guide that will guide you through the simple task of creating your 1st Database Instance as a Cloud Service.

This exercise will also give you an insight as to how easy it is to provision either a standalone database or a 2-node RAC database.

Happy Clouding!

Unable to empty RECYCLEBIN even after PURGE DBA_RECYCLEBIN

Recently, while upgrading a database from 11g to 12c, I faced an issue where the RECYCLEBIN just wouldn't empty even after multiple attempts at PURGE DBA_RECYCLEBIN command. Obviously, as you may have already guessed - this is a Bug! 

As usual the 1st line of solution is the Oracle Support documentation, and this is what saved the day.

Unable To Empty or delete rows from Sys.recyclebin$ which is causing dbua (upgrade) stopped and purge dba_recyclebin not helping (Doc ID 1910945.1)

Solution is to manually truncate the recyclebin$.
This can be done by following the simple below steps.

spool truncate_recyclebin.txt

alter system set recyclebin=off scope=spfile;
shutdown immediate
startup
purge recyclebin;
purge dba_recyclebin;
show recyclebin
show dba_recyclebin
select count(*) from sys.RECYCLEBIN$;
select OBJ#,OWNER#,SPACE,ORIGINAL_NAME,PURGEOBJ from sys.RECYCLEBIN$;
truncate table sys.RECYCLEBIN$;
execute dbms_stats.gather_table_stats('SYS','RECYCLEBIN$');
show recyclebin
show dba_recyclebin
select count(*) from sys.RECYCLEBIN$;
select OBJ#,OWNER#,SPACE,ORIGINAL_NAME,PURGEOBJ from sys.RECYCLEBIN$;
purge recyclebin;
purge dba_recyclebin;
show recyclebin
show dba_recyclebin
select count(*) from sys.RECYCLEBIN$;
select OBJ#,OWNER#,SPACE,ORIGINAL_NAME,PURGEOBJ from sys.RECYCLEBIN$;
alter system set recyclebin=on scope=spfile;
shutdown immediate
startup

spool off

I was then able to proceed with my database upgrade, and it's running fine in Oracle 12c as of now.