DBA to DA

Thursday, December 10, 2015

Oracle ODA (Database Appliance) - Database upgrade using OAK

chaitu December 10, 2015

In this blog, I am going to discuss how to upgrade database on an ODA using 'oak' from 11.2.0.3 to 11.2.0.4.
I will be mentioning what if - in case the DB has a standby, but the focus of this blog is just upgrade.

Steps which are going to be followed -

1. Ensure backups have completed successfully.
2. Put OEM blackout in place.
3. Check dbhomes and databases using 'oakcli'. (Refer to End of Blog how to create a new Home)
4. Upgrade
5. Post Steps

Assuming Step 1 & 2 are complete, jumping to step 3 -

Verify DB Homes

[root@testoda1 ]# /opt/oracle/oak/bin/oakcli show dbhomes
Oracle Home Name Oracle Home version Home Location
---------------- ------------------- ------------
OraDb11203_home1 11.2.0.3.15(20760997,17592127) /u01/app/oracle/product/11.2.0.3/dbhome_1
OraDb11204_home1 11.2.0.4.8(21352635,21352649) /u01/app/oracle/product/11.2.0.4/dbhome_1

List out your databases

[root@testoda1 ]# /opt/oracle/oak/bin/oakcli show databases
Name Type Storage HomeName HomeLocation Version
----- ------ -------- -------------- ---------------- ----------
MYTEST SINGLE ASM OraDb11203_home1 /u01/app/oracle/product/11.2.0.3/dbhome_1 11.2.0.3.15(20760997,17592127)
NEWTST SINGLE ASM OraDb11203_home1 /u01/app/oracle/product/11.2.0.3/dbhome_1 11.2.0.3.15(20760997,17592127)

Upgrade the database using OAKCLI.

(Note - We need to upgrade database from first node of ODA only)

You will be prompted for sys password, so make a note of it before hand.

[root@testoda1 ~]# oakcli upgrade database -db MYTEST -to OraDb11204_home1
INFO: 2015-12-10 00:05:51: Look at the log file '/opt/oracle/oak/log/testoda1/tools/12.1.2.5.0/dbupgrade.log' for more details

Please enter the 'SYS' password :
Please re-enter the 'SYS' password:
INFO: 2015-12-10 00:07:37: Upgrading the database MYTEST. It will take few minutes. Please wait...
...
SUCCESS: 2015-12-10 00:16:12: Successfully upgraded the database MYTEST

When the upgrade is running it is a good practice to have another session open and do a tail of the log file (highlighted above). From the log file you can figure out couple of things - one that this is oda and the name of the oda is testoda and the oak version is 12.1.2.5.0

Another point to note here is that oakcli calls dbua in silent mode and actually takes care of any pre steps such as dict stats gather.
When it is running you can verify by grepping for child processes (or ps -ef | grep -i dbua)

Check databases using OAKCLI.

[root@testoda1 ~]#oakcli show databases
Name Type Storage HomeName HomeLocation Version
----- ------ -------- -------------- ---------------- ----------
MYTEST SINGLE ASM OraDb11204_home1 /u01/app/oracle/product/11.2.0.4/dbhome_1 11.2.0.4.8(21352635,21352649)
NEWTST SINGLE ASM OraDb11203_home1 /u01/app/oracle/product/11.2.0.3/dbhome_1 11.2.0.3.15(20760997,17592127)

Assuming all went fine, the DB at this point has been upgraded and can is verified above (note the home location highlighted)

Post Steps
i. If you have any scripts pointed to old home, make sure you change them
ii. Change the home location in Enterprise Manager and remove the blackout
iii. Verify the config using srvctl. Note - oak will automatically change the OCR entry, but it is a good practice to verify the config (srvctl config database -d <db_unique_name>

Standby Database Steps
In the following section let's discuss what are the changes if there is standby database
1. Stop The standby before Upgrade
2. Change the spfile/pfile location and oratab entries - update to the new home
3. change the OCR entry using srvctl to the new home
4. Once upgrade is complete start the standby and the recovery

So, this completes the upgrade of a database on ODA using oakcli.

*******
In case your DB home is not previously installed and you want to create a new DB Home and register with OAK - Below steps are to be followed first -

cd /opt/oracle/oak/bin
./oakcli unpack -package <absolute_path_to_patch_file p14777276_210000_Linux-x86-64.zip>

cd /opt/oracle/oak/bin
./oakcli create dbhome -version 12.1.0.2.0

Monday, November 30, 2015

Oracle Database 12c - Statistics Gather Reporting

PT Anonymous November 30, 2015

Database 12c came up with a brilliant feature of reporting of stats, i.e, you can run stats gather in reporting mode, rather than just running it.

This will tell you on what all tables statistics are going to be gathered when you run your stats gather procedure with specific arguments.

6 new procedures introduced for this
dbms_stats.report_gather_?_stats
where ? can be any of - Table, schema, dictionary, database, fixed_obj, auto

All the arguments to gather_stats procedure are valid and 2 new arguments introduced which are -

1. detail_level - basic, typical, and all; depending on the type of details. typical is the deafult value
2. format - xml, html, and text. Text is the default value (I personally prefer html)

Below is an example of getting details of getting schema stats gather.

SQL> variable report_out clob;
SQL> set long 10000000
SQL> exec :report_out:=DBMS_STATS.REPORT_GATHER_SCHEMA_STATS ( ownname => 'MY_SCHEMA', -
estimate_percent => 30, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
granularity => 'ALL', -
cascade => TRUE, -
detail_level => 'ALL', -
format => 'HTML');
SQL> spool /tmp/stats_report.html
SQL> print report_out
..

Below is a screenshot from the html file output. I have truncated the output and kept screenshot of only relevant data to this discussion.

As is clear from the screenshot, we get a picture on what all tables the stats gather will take place.
This is a really good feature, helping in developing a good stats gather technique..

Tuesday, November 3, 2015

Exadata - ExaWatcher configuration

Exadata Anonymous November 03, 2015

Exawatcher is the elder brother or say sibling to OSWatcher on Exadata. It has additional diagnostics options for RDS logs/diags which are specific to Exadata only.

In this blog we see how to change the destination and the total available size for Exawatcher Logs.

I can wrap it up in 3 steps
1. Change the config
2. Stop Exawatcher
3. Start Exawatcher

Pretty neat !!

Now let's do it

You cd to opt/oracle.ExaWatcher/archive and edit the file ExaWatcher.conf

Look for the below section precisely (it is the first section generally)
.
.
.
<ResultDir> /opt/oracle.ExaWatcher/archive
<ZipOption> bzip2
<SpaceLimit> 6047
.
.
Parameters we are looking for are ResultDir and Space Limit.

Above is the default configuration (for X5 ODA, generally is similar for all versions of Exa Hardware)

Next we edit this to our own mount point and custom size so that it looks something like

[root@Myexadata01 oracle.ExaWatcher]# cat ExaWatcher.conf | grep -E 'Result|Space' | tail -2
<ResultDir> /mymount/Myexadata01
<SpaceLimit> 40960

What I have done here is redirected the output to a NAS device and then increased the space available. With all defaults 40960 should be good for around 40-45 days and Exawatcher will not clean it

On a side note cleaning of the logs is done by exawatcher script only, on the logic that it wipes clean data if mount on which it is writing is 80% or more utilized.

Okay, coming back now all we need to do is stop and start.

We are still in the /opt/oracle.ExaWatcher directory

[root@Myexadata01 oracle.ExaWatcher]# ./ExaWatcher.sh --stop

41500 ? SN 0:00 /bin/bash ./ExaWatcher.sh --fromconf

[INFO ] ExaWatcher successfully terminated!

so --stop does the stop, but starting is bit different, Below is how to start

[root@Myexadata01 oracle.ExaWatcher]# /opt/oracle.cellos/validations/bin/vldrun.pl -script oswatcher

Logging started to /var/log/cellos/validations.log

Command line is /opt/oracle.cellos/validations/bin/vldrun.pl -script oswatcher

Run validation oswatcher - PASSED

Wait for a a minute or so and Exawatcher will come up, you can see check out the status using ps utility. I have taken out one line to highlight the difference.

ps -ef | grep -i exaw

sh -c /usr/bin/vmstat 5 2 >> /mymount/Myexadata01/Vmstat.ExaWatcher/2015_11_03_04_14_00_VmstatExaWatcher_Myexadata01.mydomain

Tuesday, October 27, 2015

Oracle Database - 12c - Real-Time Database Operation Monitoring

PT Anonymous October 27, 2015

In the latest release of Oracle - 12c, Oracle has come up with another brilliant feature for performance monitoring which is Real-Time Database Operation Monitoring.

Real time Sql monitoring was introduced in 11g and helped monitoring sql's in real time.
However, only individual sql's could be monitored. Suppose there is a batch operation or a pl/sql block having multiple calls inside it which might not trigger real-time monitoring by default though it takes a lot of DB and CPU time.

This is where real time database operation monitoring comes in.

The concept and the way to do is straight forward, let me demonstrate.

Execute Start of Operation
-- Do Action ----
End of Operation.

Below is my script which I will be using in this context. I have highlighted in green start of operation and end of operation.

I will be using the infamous scott schema and a cartersian join on dba_objects to have a long running query.

var db_opid number;

EXEC :db_opid := dbms_sql_monitor.begin_operation ('Scott.Batch.Select', forced_tracking => 'Y');

select * from emp a, dept d;
selet * from emp;
select /*+ MONITOR */ * from dba_objects a, dba_objects b;

EXEC dbms_sql_monitor.end_operation('Scott.Batch.Select', :db_opid );

Note -
I have purposefully inserted the monitor hint because I wanted to monitor the sql separately in real time as well.
If you skip the monitor hint then the db decides (on the 5 second and parallel rule) that if the sql has to be monitored or not.
I have used forced_tracking as well to force monitoring of the operation.
Scott.Batch.Select is the name given to the operation which we would see in the performance page.

I am using EM Express but you can find the same stuff in Cloud Control as well.

From the Image 1 below - you can see 2 elements being monitored
One is the sql because of the monitor hint and other is the database operation.

From the Image 2 you can see new options added for filtering the type of operation in the Performance Page

Thursday, October 22, 2015

Oracle Database - 12c - Data Pump Error during ORA-1031 during export of SYS tables

Admin Anonymous October 22, 2015

Issue occurs when you do a full export of your 12c (mine is 12.1.0.2) database with export of few SYS tables.
This is when you do a consistent export using flashback_time or flashback_scn parameter
Even if you have the role - DATAPUMP_EXP_FULL_DATABASE

when you scan your log file, you will see multiple errors similar to below -

ORA-31693: Table data object "SYS"."KU$_USER_MAPPING_VIEW" failed to load/unload and is being skipped due to error: