Oracle DBA – A lifelong learning experience

Archive for the ‘11g new features’ Category

Downgrading a RAC database from to

Posted by John Hallas on May 4, 2017

It is not often that I see a database downgrade activity performed and so I thought it would be worthwhile just noting how it was done.
 2 node RAC database to, downgraded the database only and not the grid home.
Downgrade taking place on HP-UX, any downgrades taking place on Windows OS have several additional steps and won’t be covered in this post. 
This database does not use Database Vault and pre-requisite compatibility checks were carried out

Assume all commands are being run on node 1, any commands that need to be run on node 2 will be explicitly stated. Any commands will be formatted in italics.

Set ORACLE_HOME to current environment.
export ORACLE_HOME= /app/oracle/product/
Tail alert log of both nodes in separate windows
tail -f /app/oracle/diag/rdbms/soapre2a/SOAPRE2A1/trace/alert_SOAPRE2A1.log
1. Stop database using srvctl on primary node
srvctl stop database -d SOAPRE2A
Monitor the alert logs to confirm when database has successfully shutdown.
2. Create pfile from spfile
sqlplus / as sysdba
SQL>create pfile=’/home/oracle/SOAPRE2_downgrade/SOAPRE2_clusterdisable.ora’ from spfile=’+DATA/SOAPRE2A/spfilesoapre2a.ora’;
3. Alter pfile CLUSTER_DATABASE parameter to FALSE
4. Recreate spfile with new parameter
sqlplus / as sysdba
SQL>create spfile=’+DATA/SOAPRE2A/spfilesoapre2a.ora’ from pfile=’/home/oracle/SOAPRE2_downgrade/SOAPRE2_clusterdisable.ora’;
5. Startup database in downgrade mode using new spfile
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
SQL>startup downgrade
6. Execute Oracle downgrade script
From the original OH –
SQL>spool /home/oracle/SOAPRE2_downgrade/downgrade.log
SQL>spool off
SQL>shutdown immediate;
This script can be run multiple times, in the event any errors are encountered correct them and rerun until completion.
7. Change environment variables and restore config files
Execute these steps on both nodes.
Alter ORACLE_HOME and PATH environment variable to point to downgraded directories, in our case for example:
export ORACLE_HOME=’/app/oracle/product/
Ensure any entries in your oratab file are also altered to reference the downgraded directory.
Copy password files and config files from current ORACLE_HOME to downgraded directory.
8. Reload version specific components
change to downgraded release home  –
cd /app/oracle/product/
SQL> sqlplus / as sysdba
SQL> startup upgrade
SQL>spool /home/oracle/SOAPRE2_donwgrade/reload.log
SQL>spool off
This step can take quite some time to complete, in our case ~2.5 hours
9. Recompile invalid objects
SQL> shutdown immediate
SQL> startup
SQL> @utlprp.sql
SQL> exit
10. Downgrade cluster services
The final step was to downgrade cluster services to our old ORACLE_HOME and version, using the following srvctl command:
srvctl downgrade database -d db-unique-name -o old_ORACLE_HOME t to_old_versnum
in our case this was the following:
srvctl downgrade database -d SOAPRE2A -o /app/oracle/product/ -t

Posted in 11g new features, Oracle | Tagged: , , | 4 Comments »

EXcellent blog post on SQL Profiles

Posted by John Hallas on July 5, 2016

Sometimes you across a blog entry and you think to yourself – ‘I wish I had written that’.

It is clear, it is on a subject you are interested in and the examples help illustrate the points clearly.

Well I did come across just such an article today. It was written by Franck Pachot and was on the subject of SQL Profiles

It was written 18 months ago and has only had 1800 hits. I think it deserves many more

Posted in 11g new features, 12c new features, Oracle | Leave a Comment »

Vote for “Create assertion” to be added

Posted by John Hallas on May 31, 2016

There is a standard for SQL code known as ANSI-92  which is adopted by many of the main RDBMS vendors. However each vendor has its own differences from the standard.

Oracle are currently taking votes on a proposal to add the CREATE ASSERTION statement to Oracle SQL on the OTN community page

Here are two examples of how the statement could be used

This SQL statement creates an assertion to demand that there’s no more than a single president among the employees:

create assertion AT_MOST_ONE_PRESIDENT as CHECK
((select count(*)
    from EMP e
   where e.JOB = 'PRESIDENT') <= 1

This SQL statement creates an assertion to demand that Boston based departments do not employ trainers:

create assertion NO_TRAINERS_IN_BOSTON as CHECK
   (not exists
    (select 'trainer in Boston'
       from EMP e, DEPT d
      where e.DEPTNO = d.DEPTNO
        and e.JOB    = 'TRAINER'
        and d.LOC    = 'BOSTON')

To implement the same functionality now you would use a combination of check constraints and/or triggers.

I have cast my vote in favour

Posted in 11g new features | Tagged: , , , , | Leave a Comment »

ORAchk is released – what does it offer ?

Posted by John Hallas on December 29, 2015

I have looked at the ORAchk tool before and thought that I must do something with it and I have now had a more detailed review of what it offers.

Firstly the history, it started off as RACchk and then morphed into ORAchk with a sibling called EXAchk. The recent release version is which is available for MoS Doc_Id 1268927.2

It can perform an ever-growing list of checks against database, OS, specific applications (EBS and Peoplesoft being two)and various permutations of all that. It is very customisable, the usage command output runs to 281 lines !!

There is now the ability to run it as part of an Apex collection so that all reports can be centralised and you can then compare the differences between one run and the previous one.

Let me give an example of the ./orachk –b output. This shows the best practises and I have selected some of the output that seemed worthy of comment. Read the rest of this entry »

Posted in 11g new features, Oracle | Tagged: , , , , , , , | Leave a Comment »

Managing plans – identifying which plans have been used

Posted by John Hallas on December 7, 2015

This blog has the aim of answering quetions about how a sql statement has performed and which plan it has used. I have asked the type of questions that we as DBAs normally have to answer and shown how an answer may be arrived at.

We know the SQL_ID and we want to see what has been happening

set long 5000 lines 160
select sql_text from dba_hist_sqltext where sql_id = '7vt1xg0afxkba';

The SQL_ID is unknown but you do know some of the sql statement that will be used

select sql_id from dba_hist_sqltext where sql_text like 'INSERT%WC_INV_ITEM_DAILY_BAL_F%';


When did a sql statement run and what happened – did it use a different plan to normal Read the rest of this entry »

Posted in 11g new features, Oracle | Tagged: , , , | 1 Comment »

Why do you need to resetlogs after a cold backup restore

Posted by John Hallas on October 19, 2015

I posted a routine on how to take a cold backup locally to disk and then restore it back in 2010. Last week I was asked in a comment ‘why did you have to open the database using resetlogs?’  A very good question I thought so I proceeded to backup and recover just as the blog showed and I now know why.

Because Oracle will not let you do otherwise

Let me run through the example again and I will add a bit of commentary.

The original blog entry was

The basis of the commands were

startup nomount
 allocate channel c1 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
 restore controlfile from ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_MOMPRE1A_S_37_P_1_T_713884663’;
alter database mount;
 allocate channel c1 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
 allocate channel c2 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
 restore database tag=’rman_backfulcold_MOMPREPERTEST’;
–YOU DO NOT NEED TO RECOVER DATABASE otherwise it rolls forward to the current time
alter database open resetlogs;

As you can see I open the database with a resetlogs and yet it is a completely clean recovery and I should be able to do a noresetlogs. Read the rest of this entry »

Posted in 11g new features, Oracle | Tagged: , , , , | 5 Comments »

Identifying database link usage

Posted by John Hallas on September 28, 2015

As part of ongoing security reviews I wanted to determine if all database links on production systems were in use. That is not very easy to do and this article is a listing of some of the options I have considered to get that information and how it is now possible from 11GR2 onwards.

The first option was to look and see if auditing can be used. The manual states “You can audit statements that refer to tables, views, sequences, standalone stored procedures or functions, and packages, but not individual procedures within packages. (See “Auditing Functions, Procedures, Packages, and Triggers” for more information about auditing these types of objects.)

You cannot directly audit statements that reference clusters, database links, indexes, or synonyms. However, you can indirectly audit access to these schema objects, by auditing the operations that affect the base table.”

So you could audit activities on a base table that a database link might utilise, probably via a synonym. However that would show all table usage but it would be very difficult to break it down to see if a database link had been involved.

On the assumption that the code has a call to “@db_link_name” you could probably trawl ASH data or v$sql to see if a reference is available. It would be more likely that a synonym would be in use and as we have said above, we cannot audit synonym usage but you could maybe find it in v$sql. Again very work intensive with no guaranteed return.

There has been an enhancement request in MoS since 2006 – search for Bug 5098260

Jared Still posted a routine, although he does not claim to be the original author,  which shows a link being actually used. However in reality that is not really a good way of capturing information across many systems unless you enable an excessive amount of tracing or monitoring across all systems. I have demoed usage of it below and it does work

I’ve created a DB link from SNAPCL1A to SNAPTM1. First I opened the DB link:


select sysdate from dual@snaptm1;


I can see my DB link being opened in v$dblink (in my own session):

select a.db_link, u.username, logged_on, open_cursors, in_transaction, update_sent

from v$dblink a, all_users u

where a.owner_id = u.user_id;
DB_LINK                        USERNAME                       LOG OPEN_CURSORS IN_ UPD
------------------------------ ------------------------------ --- ------------ --- ---
SNAPTM1                        SYS                            YES            0 YES NO

The following script can be used to see open DB link sessions (on both databases). It can be executed from any session and it will only show open DB links (that have not been committed, rolled back or manually closed/terminated on the origin database):

col origin for a30

col "GTXID" for a30

col lsession for a10

col username for a20

col waiting for a50

Select /*+ ORDERED */

substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10)      "ORIGIN",

substr(g.K2GTITID_ORA,1,35) "GTXID",

substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,




0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),




) "State",

substr(w.event,1,30) "WAITING"

from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2

where  g.K2GTDXCB =t.ktcxbxba

and   g.K2GTDSES=t.ktcxbses

and  s.addr=g.K2GTDSES

and  w.sid=s.indx

and s2.sid = w.sid;


On the origin database:


ORIGIN                         GTXID                          LSESSION   USERNAME             State    WAITING
------------------------------ ------------------------------ ---------- -------------------- -------- --------------------------------------------------
teora01x-3762                  SNAPCL1A.cc76ea8a.7.32.983     125.5      SYS                  INACTIVE SQL*Net message from client


On the destination database:


ORIGIN                         GTXID                          LSESSION   USERNAME             State    WAITING
------------------------------ ------------------------------ ---------- -------------------- -------- --------------------------------------------------
teora01x-4065                  SNAPCL1A.cc76ea8a.7.32.983     133.599    SYSTEM               INACTIVE SQL*Net message from client


Now, I rollback my session on the origin database:


SQL> rollback;
Rollback complete.

If I query the v$dblink view, I still see my link there, but the transaction is closed now:


select a.db_link, u.username, logged_on, open_cursors, in_transaction, update_sent

from v$dblink a, all_users u

where a.owner_id = u.user_id  2    3  ;


DB_LINK                        USERNAME             LOG OPEN_CURSORS IN_ UPD
------------------------------ -------------------- --- ------------ --- ---
SNAPTM1                        SYS                  YES            0 NO  NO

The script will not return anything at this point:

SQL> Select /*+ ORDERED */

substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10)      "ORIGIN",

substr(g.K2GTITID_ORA,1,35) "GTXID",

substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,




0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),




) "State",

substr(w.event,1,30) "WAITING"

from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2

where  g.K2GTDXCB =t.ktcxbxba

and   g.K2GTDSES=t.ktcxbses

and  s.addr=g.K2GTDSES

and  w.sid=s.indx

and s2.sid = w.sid;

no rows selected 

However since at least Oracle have provided a better means of identifying database link usage after the event and not just during

Databases TST11204 and QUICKIE on different servers  -both have dblinks to each other –


create user dblinktest identified by Easter2012 ;

grant create session, create database link to dblinktest;

SQL> connect dblinktest/xxxxxxxxxx


SQL>  select * from test@quickie;

C1 C2

---------- -----

5 five

create database link TST11204 connect to dblinkrecd identified by xxxxxx using 'TST11204';

select * from test@TST11204

At this point we have made a connection  – lets see what we can find out about it. I would advise using the timestamp# column of aud$ to reduce the volume of data that has to be searched.

SQL> select userid, terminal, comment$text from sys.aud$ where comment$text like 'DBLINK%';



This information is in both source and target databases

It will return the source of a database link session. Specifically, it returns a string of the form:

SOURCE_GLOBAL_NAME=dblink_src_global_name, DBLINK_NAME=dblink_name, SOURCE_AUDIT_SESSIONID=dblink_src_audit_sessionid


dblink_src_global_name is the unique global name of the source database

dblink_name is the name of the database link on the source database

dblink_src_audit_sessionid is the audit session ID of the session on the source database that initiated the connection to the remote database using dblink_name.

So hopefully that might help in identifying if a database link is still in use or when it was last used and it can be used as another part of your security toolkit.


Posted in 11g new features, Oracle | Tagged: , , , | 2 Comments »


Posted by John Hallas on August 13, 2015


We have a number of updates to partitioned tables that are run from within pl/sql blocks which have either an execute immediate ‘alter session enable parallel dml’ or execute immediate ‘alter session force parallel dml’ in the same pl/sql block. It appears that the alter session is not having any effect as we are ending up with non-parallel plans. When the same queries are run outside pl/sql either in sqlplus or sqldeveloper sessions the updates are given a parallel plan. We have a simple test pack that we have used to prove that this anomaly takes place at (which is the version of the affected DB) and at (to show that it is not an issue with just that version).
It appears that the optimizer is not aware of the fact that the alter session has been performed. We have also tried performing the alter session statement outside of the pl/sql block i.e. in native sqlplus environment, that also does not result in a parallel plan

Let me show a test case Read the rest of this entry »

Posted in 11g new features, Oracle | Tagged: , , , , | 5 Comments »

Migrating tablespaces across Endian platforms

Posted by John Hallas on August 13, 2015

This is a set of posts about migrating a database from one endian platform to another.

The long-term intention is to move a large (10Tb) database on HP-UX to an OEL Linux server with minimum outage so that will include a database upgrade as well.

This first post is about migrating a self-contained set of schemas using transportable tablespace.

The HP-UX database is called HPUXDB and it was created through dbca with the sample schemas created.. The target database is on OEL 5.8

I already have an installation on the OEL 5.8 server and the target database is called L18

Let’s check the endian versions



4 HP-UX IA (64-bit) 
13 Linux x86 64-bit&nbsp;&nbsp; Little

Read the rest of this entry »

Posted in 11g new features, ASM, Oracle | Tagged: , , , | Leave a Comment »

Discrepancies in v$parameter default values in 12c

Posted by John Hallas on April 10, 2015

In my last blog about security parameters I mentioned I had found some oddities in the default values for parameters in, this is a more in-depth analysis of my findings.

Taking the parameter SEC_RETURN_SERVER_RELEASE_BANNER as an example.

Prior to 12c the default value for this parameter was ‘FALSE’, whereas the documentation for 12c ( states that the default is ‘TRUE’.

To confirm this, I made a connection to a 12c ( database and ran the following query:

select name, value,  default_value,  isdefault

from v$parameter

where name = 'sec_return_server_release_banner';
NAME                                     VALUE               DEFAULT_VALUE       ISDEFAULT
---------------------------------------- -------------------- -------------------- ---------
sec_return_server_release_banner         FALSE               TRUE                 TRUE

After confirming that the parameter had not been explicitly set in the parameter file, or as part of an alter system/session command, we could see that the actual value, held in ‘VALUE’, given to the parameter does not match the value provided by ‘DEFAULT_VALUE’ nor did it match the value it should have been assigned according to the documentation. Read the rest of this entry »

Posted in 11g new features, 12c new features, Oracle | Tagged: , , , , , , , | 2 Comments »