Use Oracle ASH to draw oracle session block chain

Updated on : 14 Jul 2019, since the original version released on 12Jul2019 had #2 bugs, which is fixed now

Objective: On our day to day DBA life, there are chances we might end up needing the db session block chain from the past, if it is current or live issue - then you have V$WAIT_CHAINS to see the block chain live.But if it is from the past, how do you pull it?

DBA_HIST_ACTIVE_SESS_HISTORY stores the information, but it needs some effort in mapping the block chain with the volume of data we have to process.

Instead that, I just used oracle's hierarchial query (thanks to Tanel Poder for sparking the idea in me) and some join tweeks to come up with the below sqls.

Version tested: 11.2.0.1


SQL1 - use the below row source [this query gives the sample sorted session, what it is doing along with what is its blocker and what is that doing, in case there is no information, it marks that session as IDLE - with username marked as NOSESSION]

with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 11:10:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID
,a.BLOCKING_SESSION
,a.BLOCKING_SESSION_SERIAL#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select * from dashmap
order by 1,2,3,4,5;

Output:

Query 2: To get a map of block chain and other session details [this query gives you a over view of all the sessions - i) sessions with no blockers ii) sessions with blockers available in ash. The only caveat is you wont get the sessions with blockers, but the blockers arent visible in ash, see the hierarchial query looks for 2 conditions, a) sessions with no blockers and b) sessions with blockers & such blockers are available in ash, I dont know today if there is way to get those sessions with blockers which arent available/visible in ash using hierarchial query]

with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 11:10:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select /*+ materialize */ user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID abino
,a.BLOCKING_SESSION absid
,a.BLOCKING_SESSION_SERIAL# abs#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select /* parallel(4) */
sampt
,ino
,sid
,s#
,stype
,pgm
,iuser
,sql
,event
,wc
,tw
,mach
,own
,obty
,obnm
,suobnm
,bino
,bsid
,bs#
,buser
,bsql
,bpgm
,bevent
,bwc
,btw
,bmach
,bown
,bobty
,bobnm
,bsuobnm
,RPAD('.', (level-1)*2, '.') ||ino||'~'||sid||'~'||s#||'~'||iuser AS tree
,level
,CONNECT_BY_ROOT (ino||'~'||sid||'~'||s#||'~'||iuser) AS root_id
,LTRIM(SYS_CONNECT_BY_PATH((ino||'~'||sid||'~'||s#||'~'||iuser), '->'),'->') AS path
,CONNECT_BY_ISLEAF AS leaf
,CONNECT_BY_ISCYCLE AS cycle
from dashmap
START WITH (abino is null
and absid is null
and abs# is null)
CONNECT BY NOCYCLE prior (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser) = (sampi||'~'||bino||'~'||bsid||'~'||bs#||'~'||buser)
ORDER SIBLINGS BY (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser)
;

Output:

Query 3: To filter out only the session blocking chain . Remeber to read the conditions the query works on from last (query#2) [this query will filter out only blocked sessions with their blocker details, kind of chain identity]

with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 12:05:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select /*+ materialize */ user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID abino
,a.BLOCKING_SESSION absid
,a.BLOCKING_SESSION_SERIAL# abs#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select *
from
(
select /* parallel(4) materialize */
sampt
,ino
,sid
,s#
,stype
,pgm
,iuser
,sql
,event
,wc
,tw
,mach
,own
,obty
,obnm
,suobnm
,bino
,bsid
,bs#
,buser
,bsql
,bpgm
,bevent
,bwc
,btw
,bmach
,bown
,bobty
,bobnm
,bsuobnm
,RPAD('.', (level-1)*2, '.') ||ino||'~'||sid||'~'||s#||'~'||iuser AS tree
,level lvl
,CONNECT_BY_ROOT (ino||'~'||sid||'~'||s#||'~'||iuser) AS root_id
,LTRIM(SYS_CONNECT_BY_PATH((ino||'~'||sid||'~'||s#||'~'||iuser), '->'),'->') AS path
,CONNECT_BY_ISLEAF AS lf
,CONNECT_BY_ISCYCLE AS cycle
from dashmap
START WITH (abino is null
and absid is null
and abs# is null)
CONNECT BY NOCYCLE prior (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser) = (sampi||'~'||bino||'~'||bsid||'~'||bs#||'~'||buser)
ORDER SIBLINGS BY (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser)
)
where (lvl>1)
;

Output: 

Remember the caveat: The only caveat  of query#2 & query#3 is you wont get the sessions with blockers, but the blockers aren't visible in ash, see the hierarchical query (#2 & #3) looks for 2 conditions, a) sessions with no blockers and b) sessions with blockers & such blockers are available in ash, I don't know today if there is way to get those sessions with blockers which aren't available/visible in ash using hierarchical query]



If you find it difficult to follow this blog, please bear with me, I will write more explanation. Also if you want the excel view of the data, please get in touch with me through feedback button.

Foot notes - 19Jul23 - to get the output formatted

set time on

set timing on

set lines 2000

set pages 49999

col sampt for a30

col pgm for a18

col iuser for a20

col event for a30

col mach for a30

col own for a30

col obty for a30

col obnm for a30

col subonm for a30

col buser for a20

col bpgm for a18

col bevent for a30

col bmach for a30

col bown for a30

col bobty for a30

col bobnm for a30

col bsuobnm for a30

col tree for a70

col root_id for a70

col path for a700

set colsep ,

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

As usual thanks for the visit :)
Have a good day.

oracle sql trick - Get distinct records from partitioned data

Dear Readers,

I was in a situation, where I needed to get distinct records from group/subgroup partitioned data.

Please note the below sqls can be executed on livesql.oracle.com. This is tested in 19c :)

Query link:  https://livesql.oracle.com/apex/livesql/s/ilxt7f2vd91u5tii3eld2brxn

Row source:

select level lvl,mod(level,2) md_lvl2,mod(level,3) md_lvl3  
from dual  
connect by level < 100

Partitioning:

select md_lvl2 
,md_lvl3 
,sum(lvl) over (partition by md_lvl2 order by md_lvl3 desc) sumrec 
from  
(  
select level lvl,mod(level,2) md_lvl2,mod(level,3) md_lvl3  
from dual  
connect by level < 100)

Final Query:

select distinct(md_lvl2||','||md_lvl3||','||sumrec) 
from 
( 
select md_lvl2 
,md_lvl3 
,sum(lvl) over (partition by md_lvl2 order by md_lvl3 desc) sumrec 
from  
(  
select level lvl,mod(level,2) md_lvl2,mod(level,3) md_lvl3  
from dual  
connect by level < 100)) 
order by 1;

Result: 

(MD_LVL2||','||MD_LVL3||','||SUMREC)
0,0,2450
0,1,1634
0,2,850
1,0,2500
1,1,1633
1,2,800

oracle sql trick - subgroup/group - percentage

Dear Readers,

I was in a situation, where I needed to form group,subgroup on a resultset. This is normally very easy in pivot operations in excel/google sheet.

Please note the below sqls can be executed on livesql.oracle.com. This is tested in 19c :)

How to get this done using oracle sql. I am writing this blog to just show that.

Query 1 - row source:

select mod(level,2),mod(level,3),level
from dual
connect by level < 10
order by 1,2;

result:
"MOD(LEVEL,2)","MOD(LEVEL,3)",LEVEL
0,0,6
0,1,4
0,2,2
0,2,8
1,0,3
1,0,9
1,1,1
1,1,7
1,2,5


query 2 - which perform grouping of lvl2 & lvl3 columns:

select md_lvl2,md_lvl3,sum(lvl)
from
(
select mod(level,2) md_lvl2,mod(level,3) md_lvl3,level lvl
from dual
connect by level < 10
)
group by md_lvl2,md_lvl3
order by  md_lvl2,md_lvl3;

result:
MD_LVL2,MD_LVL3,SUM(LVL)
0,0,6
0,1,4
0,2,10
1,0,12
1,1,8
1,2,5

See here I can get grouping done @ 1 level which is @ both the columns, but now my requirement is I need a ratio or percentage of this resultant sum over lvl2 aggregation. Hope you follow me

for example, I need the ratio of sum(lvl) aggregated over md_lvl2,md_lvl3 over sum(lvl) aggregated over md_lvl2, kind a rollup - but here I need percentage.

Query 3 - the trick/the sql for requirement:

with t as 
(
select mod(level,2) md_lvl2,mod(level,3) md_lvl3,level lvl
from dual
connect by level < 10
)
select md_lvl2
,md_lvl3
,sum_lvl3
,sum_lvl2
,round(100*sum_lvl3/sum_lvl2,2) ratio
from
(
select md_lvl2
,md_lvl3
,sum(lvl) over (partition by md_lvl2,md_lvl3) sum_lvl3
,sum(lvl) over (partition by md_lvl2) sum_lvl2
from t
)
group by md_lvl2
,md_lvl3
,sum_lvl3
,sum_lvl2
order by 1,2;

Query:
MD_LVL2,MD_LVL3,SUM_LVL3,SUM_LVL2,RATIO
0,0,6,20,30
0,1,4,20,20
0,2,10,20,50
1,0,12,25,48
1,1,8,25,32
1,2,5,25,20

See if you notice we get the percentage out now. The group by expression is mandatory so that you get unique records out. Otherwise all 9 records gets with duplicate on md_lvl2,md_lvl3 gets printed, but it doesn harm the percentage or sum.

Thanks for paying a visit.

Oracle Hierarchical Query Example - Multiple Columns on connect by clause

Oracle Hierarchical Query Example  - Multiple Columns on connect by clause

Dear Readers,

This post is really a quick one where you can see how can we write a Hierarchical Query CONNECT BY Clause with multiple columns.

Good Reads:

Thanks to Tim Hall for his tutorial here a quick start one and then oracle guide here.

Setup:

CREATE TABLE tab1
(
  id        NUMBER,
  name      varchar2(10),
  parent_id NUMBER,
  pname      varchar2(10)
);

INSERT INTO tab1 VALUES (1, 'ram',NULL,NULL);
INSERT INTO tab1 VALUES (2, 'rose',1,'ram');
INSERT INTO tab1 VALUES (3, 'kim',2,'rose');
INSERT INTO tab1 VALUES (4, 'kuber',2,'rose');
COMMIT;

Query:

SELECT id||','||name id_nm,
       parent_id||','||pname pid_nm,
       RPAD('.', (level-1)*2, '.') || id||','||name AS tree,
       level,
       CONNECT_BY_ROOT (id||','||name) AS root_id,
      LTRIM(SYS_CONNECT_BY_PATH((id||','||name), '->'),'->') AS path,
       CONNECT_BY_ISLEAF AS leaf
FROM   tab1
START WITH (parent_id||pname) IS NULL
CONNECT BY prior (id||name) = (parent_id||pname)
ORDER SIBLINGS BY (id||name)
;

Result:

ID_NM      PID_NM     TREE                      LEVEL ROOT_ID    PATH                           LEAF
---------- ---------- -------------------- ---------- ---------- ------------------------ ----------
1,ram      ,          1,ram                         1 1,ram      1,ram                             0
2,rose     1,ram      ..2,rose                      2 1,ram      1,ram->2,rose                     0
3,kim      2,rose     ....3,kim                     3 1,ram      1,ram->2,rose->3,kim              1
4,kuber    2,rose     ....4,kuber                   3 1,ram      1,ram->2,rose->4,kuber            1
 
 Thanks for your visit!

TNS entry addition using ansible

Dear Readers,

In continuation to the previous blogpost narrated here. I have put out a blog on adding a new tns entry as you see below...

I will try to upload the code to git.

Ansible notes:

A) Read the note on this below link for order of precedence of the variable

link: https://docs.ansible.com/ansible/latest/user_guide/playbooks_variables.html#variable-precedence-where-should-i-put-a-variable

background: Variable declaration, it is possible we define host_vars & group_vars to define host and group specific variables, but here for this example, I am going to

keep the variable declaration very simple (do it in inventory file and ensure the same variable isnt used any where else with higher precedence).

B) Ansible config search order [from actual ansible.cfg file]

# nearly all parameters can be overridden in ansible-playbook
# or with command line flags. ansible will read ANSIBLE_CONFIG,
# ansible.cfg in the current working directory, .ansible.cfg in
# the home directory or /etc/ansible/ansible.cfg, whichever it
# finds first

C) I used ANSIBLE_GATHERING='explicit' to avoid fact collection each time for my tests.

TNS addition:

1) Setting up ansible config, ansible inventory files.

2) Find out the tnsnames.ora file path corresponding to the db

3) Verify that the tnsnames.ora file exists already and it doesnt have the TNS entry we are about to add

4) Create a directory under $OH as follows
$OH/network/admin/TNS_Ansible

5) Whenever we add a TNS entry create a temp file tnsadmin.ora with the TNS entry under the above temp tns directory

6) Set TNS_ADMIN directory to the new ansible temp tns directory created and test weather the tnsping works fine.

7) If it does work, append this entry directly from this temporary TNS file to the original file and test weather the tnsping still works.

8) Archive the temporary tnsadmin.ora under the TNS_Asnible directory with a date directory, so it can be used for troubleshooting purpose later.


========

Inputs for this excercise: server name and DB name inventory file.

Step 1) Setting up ansible config, ansible inventory files

It is better to keep an ansible config file on your local home where you have RW permisson, so you can control your ansible environment.

My ansible home is: /home/oracle/dba/ansi-inventory
By default we get /etc/ansible/ansible.cfg with ansible install, you can copy/edit this file on your ansible home directory if you wish.
My ansible.cfg file has the following setup... [i dont have this "#" symbol line, this is for readability I put it here]

##########################################################
[defaults]
inventory=./inventory_file.txt
forks=3
log_path=/home/oracle/dba/ansi-inventory/ansible.log
##########################################################

Each of the parameter has a meaning to it, specifically the forks tells how many nodes in parallel we need to act up on. In my case if I have 6 nodes, 3 nodes will be

acted up on parallel at a time.

Ansible inventory, to start with I kept a very simple inventory with the needed variable.

##########################
cat inventory_file_2.txt
[servers]
node1 tns_db="db1" tns_alias="db_new"
##########################

Here node1 is the node name and tns_db is the variable name, I should ensure I dont have any other places where this tns_db is declared. Also the tns_alias is the new

tns entries alias I am going to make.

Now we have ansible inventory and ansible config file prepared. Now let us draft a play book to act on this nodes for the variable declared.

Step 2) Find out the tnsnames.ora file path corresponding to the db.

##begin from below
- hosts: "{{ node_list }}"

  tasks:
    - name: Discover DB existance in oratab
      shell:
        cat /etc/oratab|grep -iw {{ tns_db }}|wc -l
      register: var_dbexist

    - name: Fail if the db doesnt exist in oratab
      fail:
        msg: "ANSI-ORAERR: Whoops! DB doesnt exist in oratab"
      when: var_dbexist.stdout|int == 0

    - name: Discover DB Home
      shell:
        echo `cat /etc/oratab|grep -iw {{ tns_db }}|awk -F ":" '{print $2}'`/network/admin/tnsnames.ora
      register: var_tnsfilepath##end here


Step 3) Verify that the tnsnames.ora file exists already and it doesnt have the TNS entry we are about to add

##begin from below
    - name: Verify tnsnames.ora file exists on the TNS_ADMIN path
      stat:
        path: "{{ var_tnsfilepath.stdout }}"
      register: var_tnsfileexist

    - name: Fail if tnsnames.ora file isnt found as expected
      fail:
        msg: "ANSI-STATERR: Whoops! File tnsnames.ora doesnt exist"
      when: var_tnsfileexist.stat.exists == False

    - name: Try do a tnsping for the entry we are about to add
      shell:
        export ORACLE_SID={{ tns_db }};export ORAENV_ASK=NO;. oraenv;
        tnsping {{ tns_alias }}
      register: var_tnsping
      ignore_errors: True

    - name: Verify the tnsping result to see if the TNS entry is already there to avoid making duplicate entries.
      fail:
        msg: "ANSI-ORAERR: Whoops! tns entry is already there"
      when: '"TNS-03505" not in var_tnsping.stdout'

    - debug: var=var_tnsping.stdout
##end here

Step 4) Create a directory under $OH as follows
$OH/network/admin/TNS_Ansible

##begin from below
    - name: Temp tns directory path
      shell:
        echo `cat /etc/oratab|grep -iw {{ tns_db }}|awk -F ":" '{print $2}'`/network/admin/tns_ansible
      register: var_temptnspath

    - name: Create the temp tns admin directory
      command: mkdir {{ var_temptnspath.stdout }}
      args:
        creates: "{{ var_temptnspath.stdout }}"

    - name: Modify the permisson of the directory
      file:
        path: "{{ var_temptnspath.stdout }}"
        owner: oracle
        group: dba
        mode: 0744
##end here

5) Whenever we add a TNS entry create a temp file tnsadmin.ora with the TNS entry under the above temp tns directory

##begin from below
    - name: Temp tnsfile path
      shell:
        echo {{ var_temptnspath.stdout }}/tnsnames.ora
      register: var_temptnsfilepath

    - name: Add the tns entry
      lineinfile:
        path: "{{ var_temptnsfilepath.stdout }}"
        create: yes
        owner: oracle
        group: dba
        mode: 0644
        line: '{{ item }}'
      with_items:
        - 'DB_NEW ='
        - ' (DESCRIPTION ='
        - ' (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))'
        - ' (CONNECT_DATA ='
        - ' (SERVER = DEDICATED)'
        - ' (SERVICE_NAME = DB1)'
        - '  )'
        - ' )'
##end here

6) Set TNS_ADMIN directory to the new ansible temp tns directory created and test weather the tnsping works fine.

##begin from below
    - name: Tnsping to verify if the new tns entry is pinging fine
      shell:
        export ORACLE_SID={{ tns_db }};export ORAENV_ASK=NO;. oraenv;export TNS_ADMIN={{ var_temptnspath.stdout }}
        tnsping {{ tns_alias }}
      register: var_tnsping
      ignore_errors: True

    - name: Verify the tnsping result to see if the TNS entry works ok
      fail:
        msg: "ANSI-ORAERR: Whoops! tnsping fails for {{ tns_alias }}"
      when: '"TNS-" in var_tnsping.stdout or var_tnsping.rc > 0'

    - debug: var=var_tnsping.stdout
##end here

7) If it does work, append this entry directly from this temporary TNS file to the original file and test weather the tnsping still works.

##begin from below
    - name: Backup existing tnsnames.ora file
      shell:
        cp {{ var_tnsfilepath.stdout }} {{ var_temptnspath.stdout }}/tnsnames.ora_savorig_`date +%Y%m%d%H%M%S`

    - name: Append a new line before the tns entry is added
      shell:
        echo "" >> {{ var_tnsfilepath.stdout }}

    - name: Append the new TNS entry to the original tnsnames.ora file and do test TNS ping
      shell:
        cat {{ var_temptnsfilepath.stdout }} >> {{ var_tnsfilepath.stdout }};
        export ORACLE_SID={{ tns_db }};export ORAENV_ASK=NO;. oraenv;
        tnsping {{ tns_alias }}
      register: var_tnsping
      ignore_errors: True

    - name: Final verifcation of the tnsping result to see if the TNS entry works ok
      fail:
        msg: "ANSI-ORAERR: Whoops! tnsping fails for {{ tns_alias }}"
      when: '"TNS-" in var_tnsping.stdout or var_tnsping.rc > 0'
##end here

step 8) Archive the temporary tnsadmin.ora under the TNS_Asnible directory with a date directory, so it can be used for troubleshooting purpose later.

##begin from below
    - name: Archive the temp ansible tns file for later troubleshooting purpose
      shell:
        mv {{ var_temptnsfilepath.stdout }} {{ var_temptnsfilepath.stdout }}_ansitemp_`date +%Y%m%d%H%M%S`
##end here

command to execute the above playbook:
ansible-playbook tnsadd.yml  -i ./inventory_file_2.txt --extra-vars="node_list=servers" -u oracle -v

result expected:

PLAY RECAP ***************************************************************************************************************
node1                     : ok=16   changed=10   unreachable=0    failed=0


Example execution:

[root@node1 ansi-inventory]# ansible-playbook tnsadd.yml  -i ./inventory_file_2.txt --extra-vars="node_list=servers" -u oracle

PLAY [servers] ***********************************************************************************************************

TASK [Discover DB existance in oratab] ***********************************************************************************
changed: [node1]

TASK [Fail if the db doesnt exist in oratab] *****************************************************************************
skipping: [node1]

TASK [Discover DB Home] **************************************************************************************************
changed: [node1]

TASK [Verify tnsnames.ora file exists on the TNS_ADMIN path] *************************************************************
ok: [node1]

TASK [Fail if tnsnames.ora file isnt found as expected] ******************************************************************
skipping: [node1]

TASK [Try do a tnsping for the entry we are about to add] ****************************************************************
fatal: [node1]: FAILED! => {"changed": true, "cmd": "export ORACLE_SID=db1;export ORAENV_ASK=NO;. oraenv; tnsping db_new", "delta": "0:00:00.267024", "end": "2019-05-

12 18:08:02.918267", "failed": true, "msg": "non-zero return code", "rc": 1, "start": "2019-05-12 18:08:02.651243", "stderr": "", "stderr_lines": [], "stdout": "The

Oracle base for ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1 is /opt/app/oracle\n\nTNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-MAY-2019

18:08:02\n\nCopyright (c) 1997, 2009, Oracle.  All rights reserved.\n\nUsed parameter files:\n/opt/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora\n\nTNS-

03505: Failed to resolve name", "stdout_lines": ["The Oracle base for ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1 is /opt/app/oracle", "", "TNS Ping Utility for

Linux: Version 11.2.0.1.0 - Production on 12-MAY-2019 18:08:02", "", "Copyright (c) 1997, 2009, Oracle.  All rights reserved.", "", "Used parameter files:",

"/opt/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora", "", "TNS-03505: Failed to resolve name"]}
...ignoring

TASK [Verify the tnsping result to see if the TNS entry is already there to avoid making duplicate entries.] *************
skipping: [node1]

TASK [debug] *************************************************************************************************************
ok: [node1] => {
    "var_tnsping.stdout": "The Oracle base for ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1 is /opt/app/oracle\n\nTNS Ping Utility for Linux: Version 11.2.0.1.0 -

Production on 12-MAY-2019 18:08:02\n\nCopyright (c) 1997, 2009, Oracle.  All rights reserved.\n\nUsed parameter files:

\n/opt/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora\n\nTNS-03505: Failed to resolve name"
}

TASK [Temp tns directory path] *******************************************************************************************
changed: [node1]

TASK [Create the temp tns admin directory] *******************************************************************************
ok: [node1]

TASK [Modify the permisson of the directory] *****************************************************************************
ok: [node1]

TASK [Temp tnsfile path] *************************************************************************************************
changed: [node1]

TASK [Add the tns entry] *************************************************************************************************
changed: [node1] => (item=DB_NEW =)
changed: [node1] => (item= (DESCRIPTION =)
changed: [node1] => (item= (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521)))
changed: [node1] => (item= (CONNECT_DATA =)
changed: [node1] => (item= (SERVER = DEDICATED))
changed: [node1] => (item= (SERVICE_NAME = db1))
changed: [node1] => (item=  ))
changed: [node1] => (item= ))

TASK [Tnsping to verify if the new tns entry is pinging fine] ************************************************************
changed: [node1]

TASK [Verify the tnsping result to see if the TNS entry works ok] ********************************************************
skipping: [node1]

TASK [debug] *************************************************************************************************************
ok: [node1] => {
    "var_tnsping.stdout": "The Oracle base for ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1 is /opt/app/oracle"
}

TASK [Backup existing tnsnames.ora file] *********************************************************************************
changed: [node1]

TASK [Append a new line before the tns entry is added] *******************************************************************
changed: [node1]

TASK [Append the new TNS entry to the original tnsnames.ora file and do test TNS ping] ***********************************
changed: [node1]

TASK [Final verifcation of the tnsping result to see if the TNS entry works ok] ******************************************
skipping: [node1]

TASK [Archive the temp ansible tns file for later troubleshooting purpose] ***********************************************
changed: [node1]

PLAY RECAP ***************************************************************************************************************
node1                     : ok=16   changed=11   unreachable=0    failed=0


Thanks for the visit. Happy learning!

Ansible for TNS management

TNS Management involves the following modules developed:

TNS entry addition
TNS entry deletion

If we make the first 2,then eventually the TNS update should be possible.

TNS addition:
1) Create a directory under $OH as follows
$OH/network/admin/TNS_Ansible

2) Whenever we add a TNS entry create a temp file tnsadmin.ora with the TNS entry under the above directory

3) Set TNS_ADMIN directory to the new ansible directory created and test weather the tnsping works fine.

4) If it does work, append this entry directly from this temporary TNS file to the original file and test weather the tnsping still works.

5) Archive the temporary tnsadmin.ora under the TNS_Asnible directory with a date directory, so it can be used for troubleshooting purpose later.

TNS deletion:
1) Backup tnsnames.ora to TNS_Ansible with time stamp

2) find out the starting and ending line # of the TNS entry to be deleted, then perform the deletion

3) Try tnsping to ensure the tnsping fails

4) Also perform a diff to verify that the backed up TNS and the actual TNS files differ only by the entry we deleted.

TNS update:

Merge of the above 2 actions...
1) Perform deletion
2) Perform new entry addition.

This is the outline of the action, as soon as the code is available, will publish.

Thanks

Test oracle backup of image copy (both backup/restore)

Lab excercise: Test oracle backup of image copy (both backup/restore)

For the lab excercise if at all we needed to use sbt library, we will use oracle.disksbt library which is a dummy library provided by oracle.

allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)';

setup:

1) create a tablespace for the testing purpose...

create tablespace backupofbackup02 datafile size 10m autoextend on next 10m;
select file_name from dba_data_files where lower(tablespace_name)='backupofbackup02';
alter user scott quota unlimited on backupofbackup02;

2) create a test table...

create table scott.test2 tablespace backupofbackup02 as
select mod(level,2) mdlvl,level lvl
from dual
connect by level < 1000;

3) Try access the table

select mdlvl,count(1)
from scott.test2
group by mdlvl;

select owner,table_name,tablespace_name
from dba_Tables
where owner='SCOTT'
and table_name='TEST2';

4) Put the tbspc in Ro mode and take a backup of the tablespace as backupset to local disk

alter tablespace BACKUPOFBACKUP02 read only;
select tablespace_name,status from dba_tablespaces where tablespace_name='BACKUPOFBACKUP02';

backup disk location: /oradata_2/oratest_21apr19

run
{
allocate channel d1 device type disk;
backup as copy tablespace BACKUPOFBACKUP02 format '/oradata_2/oratest_21apr19/backup_tbspc_%s_%t_%T';
}

5) Try backing up the copy of the tbspc to disk and sbt.

to backup backupset...
BACKUP BACKUPSET command

to backup copy backups...
backup copy of
BACKUP DATAFILECOPY FROM TAG

option disk:

list copy of tablespace BACKUPOFBACKUP02;

run
{
allocate channel d1 device type disk;
backup copy of tablespace BACKUPOFBACKUP02 format '/oradata_2/oratest_21apr19_dkch/backupdk_tbspc_%d_%s_%t_%T';
}

option tape:

run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_21apr19_sbtch)';
backup copy of tablespace BACKUPOFBACKUP02 format 'backuptp_tbspc_%d_%s_%t_%T';
}

Notice here, we arent calling out any specific backup to be backedup, we are just calling the db object, which means by default the latest copy of tablespace will be up for backup. But by mentioing tag you can select old backup as well.
[read here: https://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmbackp.htm#CEGBCHJA]

6) Try to restore the tablespace from each of this backupset copies and try fetch the records from the test table.

option tape backup of image copy:

i) list backup of tablespace BACKUPOFBACKUP02 summary;
   list backup of tablespace BACKUPOFBACKUP02;
   list copy of tablespace BACKUPOFBACKUP02;
   

ii) manually delete the file from disk /oradata_1/oratest/datafile/o1_mf_backupof_gcrhsodq_.dbf

rm /oradata_1/oratest/datafile/o1_mf_backupof_gcrhsodq_.dbf

Try data selection to ensure the accidental damage is done... [if this retrives the rows, try disc from current session, flush the buffer cache and retry]

select mdlvl,count(1)
from scott.test2
group by mdlvl;

select error from v$datafile_header where file#=1015;

>> verify your getting "CANNOT OPEN FILE" message.

iii) To facilitate a tablespace restore on a nonarchivelog db, please keep the tablespace in offline state.Otherwise you may hit ORA-19573 & skipping datafile 1014; already restored to file errors.

alter tablespace BACKUPOFBACKUP02 offline;

iv) restore from the tape backupset:
run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_21apr19_sbtch)';
restore tablespace BACKUPOFBACKUP02;
}

v) Bring the tablespace to online
alter tablespace BACKUPOFBACKUP02 online;

vi) Try data selection

select mdlvl,count(1)
from scott.test2
group by mdlvl;

option disk backupset (using tag to pick the disk backup):

i) Archive the previous results.

ii) manually delete the file from disk /oradata_1/oratest/datafile/o1_mf_backupof_gcrjv6tx_.dbf  [the name of the file may change based on the previous restore since we are using OMF for this test]

rm /oradata_1/oratest/datafile/o1_mf_backupof_gcrjv6tx_.dbf

Try data selection to ensure the accidental damage is done... [if this retrives the rows, try disc from current session, flush the buffer cache and retry]

select mdlvl,count(1)
from scott.test2
group by mdlvl;

select error from v$datafile_header where file#=1014;

>> verify your getting "CANNOT OPEN FILE" message.

iii) To facilitate a tablespace restore on a nonarchivelog db, please keep the tablespace in offline state.Otherwise you may hit ORA-19573 & skipping datafile 1014; already restored to file errors.

alter tablespace BACKUPOFBACKUP02 offline;

iv) restore from the backupset copy on disk (#2) -- let us try a default restore using disk channel and observe what happens, then let us force it to a specific copy by renaming the other copy.
run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP02 from tag TAG20190421T180721;
}

--- it properly picks the copy#1 first and incase the copy#1 isnt available it fails over to the next one which is copy#2 [you will notice messages like "failover to piece handle=" in rman restore when copy#1 is inaccessible].

v) Bring the tablespace to online
alter tablespace BACKUPOFBACKUP01 online;

vi) Try data selection

select mdlvl,count(1)
from scott.test2
group by mdlvl;

vii) Query and store the v$rman_status


select START_TIME,END_TIME,OPERATION,status,MBYTES_PROCESSED,INPUT_BYTES,OUTPUT_BYTES,OPTIMIZED,OUTPUT_DEVICE_TYPE
from v$rman_status
where END_TIME > sysdate -1/24 and operation!='LIST'
order by start_time;

repeat this excercise for...

option image copy:

run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP02 from tag TAG20190421T180458;
}

Execution:

########### Step 1)

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='BACKUPOFBACKUP02';

no rows selected

SQL> select tablespace_name,status from dba_tablespaces where upper(tablespace_name)='BACKUPOFBACKUP02';

no rows selected

SQL> create tablespace backupofbackup02 datafile size 10m autoextend on next 10m;

Tablespace created.

SQL> select file_name from dba_data_files where lower(tablespace_name)='backupofbackup02';

FILE_NAME
--------------------------------------------------------------------------------
/oradata_1/oratest/datafile/o1_mf_backupof_gcrhsodq_.dbf

SQL> alter user scott quota unlimited on backupofbackup02;

User altered.

########### Step 2)

SQL> create table scott.test2 tablespace backupofbackup02 as
select mod(level,2) mdlvl,level lvl
from dual
connect by level < 1000;  2    3    4

Table created.

########### Step 3)

SQL> select mdlvl,count(1)
from scott.test2
group by mdlvl;  2    3

     MDLVL   COUNT(1)
---------- ----------
         1        500
         0        499


SQL> select owner,table_name,tablespace_name
from dba_Tables
where owner='SCOTT'
and table_name='TEST2';  2    3    4

OWNER      TABLE_NAME   TABLESPACE_NAME
---------- ------------ ------------------------------
SCOTT      TEST2        BACKUPOFBACKUP02

########### Step 4)

RMAN> run
{
allocate channel d1 device type disk;
backup as copy tablespace BACKUPOFBACKUP02 format '/oradata_2/oratest_21apr19/backup_tbspc_%s_%t_%T';
}2> 3> 4> 5>

allocated channel: d1
channel d1: SID=271 device type=DISK

Starting backup at 21-APR-19
channel d1: starting datafile copy
input datafile file number=01015 name=/oradata_1/oratest/datafile/o1_mf_backupof_gcrhsodq_.dbf
output file name=/oradata_2/oratest_21apr19/backup_tbspc_104_1006193098_20190421 tag=TAG20190421T180458 RECID=2 STAMP=1006193100
channel d1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 21-APR-19

Starting Control File and SPFILE Autobackup at 21-APR-19
piece handle=/oradata_1/fast_recovery_area/oratest/autobackup/2019_04_21/o1_mf_s_1006193102_gcrj2jj1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-APR-19
released channel: d1

RMAN> list copy of tablespace BACKUPOFBACKUP02;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
2       1015 A 21-APR-19       5741170    21-APR-19
        Name: /oradata_2/oratest_21apr19/backup_tbspc_104_1006193098_20190421
        Tag: TAG20190421T180458


RMAN>

########### Step 5)

>>>>>>>>>>>option disk:

RMAN> list backup of tablespace BACKUPOFBACKUP02 summary;

specification does not match any backup in the repository


RMAN> list copy of tablespace BACKUPOFBACKUP02;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
2       1015 A 21-APR-19       5741170    21-APR-19
        Name: /oradata_2/oratest_21apr19/backup_tbspc_104_1006193098_20190421
        Tag: TAG20190421T180458


RMAN> run
{
allocate channel d1 device type disk;
backup copy of tablespace BACKUPOFBACKUP02 format '/oradata_2/oratest_21apr19_dkch/backupdk_tbspc_%d_%s_%t_%T';
}2> 3> 4> 5>

allocated channel: d1
channel d1: SID=271 device type=DISK

Starting backup at 21-APR-19
channel d1: starting full datafile backup set
channel d1: including datafile copy of datafile 01015 in backup set
input file name=/oradata_2/oratest_21apr19/backup_tbspc_104_1006193098_20190421
channel d1: starting piece 1 at 21-APR-19
channel d1: finished piece 1 at 21-APR-19
piece handle=/oradata_2/oratest_21apr19_dkch/backupdk_tbspc_ORATEST_106_1006193241_20190421 tag=TAG20190421T180721 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-APR-19

Starting Control File and SPFILE Autobackup at 21-APR-19
piece handle=/oradata_1/fast_recovery_area/oratest/autobackup/2019_04_21/o1_mf_s_1006193243_gcrj6wjw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-APR-19
released channel: d1

RMAN> list copy of tablespace BACKUPOFBACKUP02;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
2       1015 A 21-APR-19       5741170    21-APR-19
        Name: /oradata_2/oratest_21apr19/backup_tbspc_104_1006193098_20190421
        Tag: TAG20190421T180458


RMAN> list backup of tablespace BACKUPOFBACKUP02 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
95      B  F  A DISK        21-APR-19       1       1       NO         TAG20190421T180721

RMAN>

Note here: the tag changes unlinke backupset the tag isnt kept as same and copy count isnt incremented, since both the backups are of different type.

>>>>>>>>>>>option tape:

RMAN> run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_21apr19_sbtch)';
backup copy of tablespace BACKUPOFBACKUP02 format 'backuptp_tbspc_%d_%s_%t_%T';
}2> 3> 4> 5>

allocated channel: t1
channel t1: SID=271 device type=SBT_TAPE
channel t1: WARNING: Oracle Test Disk API

Starting backup at 21-APR-19
channel t1: starting full datafile backup set
channel t1: including datafile copy of datafile 01015 in backup set
input file name=/oradata_2/oratest_21apr19/backup_tbspc_104_1006193098_20190421
channel t1: starting piece 1 at 21-APR-19
channel t1: finished piece 1 at 21-APR-19
piece handle=backuptp_tbspc_ORATEST_108_1006193400_20190421 tag=TAG20190421T180959 comment=API Version 2.0,MMS Version 8.1.3.0
channel t1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-APR-19

Starting Control File and SPFILE Autobackup at 21-APR-19
piece handle=c-2907708373-20190421-02 comment=API Version 2.0,MMS Version 8.1.3.0
Finished Control File and SPFILE Autobackup at 21-APR-19
released channel: t1

RMAN>

RMAN> list copy of tablespace BACKUPOFBACKUP02;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
2       1015 A 21-APR-19       5741170    21-APR-19
        Name: /oradata_2/oratest_21apr19/backup_tbspc_104_1006193098_20190421
        Tag: TAG20190421T180458


RMAN> list backup of tablespace BACKUPOFBACKUP02 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
95      B  F  A DISK        21-APR-19       1       1       NO         TAG20190421T180721
97      B  F  A SBT_TAPE    21-APR-19       1       1       NO         TAG20190421T180959

RMAN>

Note: Even after sending backupset to tape,we get to see one more tag and the copy# isnt incremented.

########### Step 6)

option tape backup of image copy:

RMAN> list backup of tablespace BACKUPOFBACKUP02 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
95      B  F  A DISK        21-APR-19       1       1       NO         TAG20190421T180721
97      B  F  A SBT_TAPE    21-APR-19       1       1       NO         TAG20190421T180959

RMAN> list copy of tablespace BACKUPOFBACKUP02;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
2       1015 A 21-APR-19       5741170    21-APR-19
        Name: /oradata_2/oratest_21apr19/backup_tbspc_104_1006193098_20190421
        Tag: TAG20190421T180458


RMAN> list backup of tablespace BACKUPOFBACKUP02;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
95      Full    1.08M      DISK        00:00:01     21-APR-19
        BP Key: 97   Status: AVAILABLE  Compressed: NO  Tag: TAG20190421T180721
        Piece Name: /oradata_2/oratest_21apr19_dkch/backupdk_tbspc_ORATEST_106_1006193241_20190421
  List of Datafiles in backup set 95
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1015    Full 5741170    21-APR-19 /oradata_1/oratest/datafile/o1_mf_backupof_gcrhsodq_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
97      Full    4.00M      SBT_TAPE    00:00:00     21-APR-19
        BP Key: 99   Status: AVAILABLE  Compressed: NO  Tag: TAG20190421T180959
        Handle: backuptp_tbspc_ORATEST_108_1006193400_20190421   Media: n01tst_21apr19_sbtc,backuptp_tbspc_RMN0
  List of Datafiles in backup set 97
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1015    Full 5741170    21-APR-19 /oradata_1/oratest/datafile/o1_mf_backupof_gcrhsodq_.dbf

RMAN>


[oracle@dbhost ~]$ ls -altr /oradata_1/oratest/datafile/o1_mf_backupof_gcrhsodq_.dbf
-rw-rw----. 1 oracle oracle 10493952 Apr 21 18:02 /oradata_1/oratest/datafile/o1_mf_backupof_gcrhsodq_.dbf
[oracle@dbhost ~]$ rm /oradata_1/oratest/datafile/o1_mf_backupof_gcrhsodq_.dbf

SQL> !ls -altr /oradata_1/oratest/datafile/o1_mf_backupof_gcrhsodq_.dbf
ls: cannot access /oradata_1/oratest/datafile/o1_mf_backupof_gcrhsodq_.dbf: No such file or directory

SQL>


SQL> select mdlvl,count(1)
from scott.test2
group by mdlvl;  2    3
from scott.test2
           *
ERROR at line 2:
ORA-01116: error in opening database file 1015
ORA-01110: data file 1015:
'/oradata_1/oratest/datafile/o1_mf_backupof_gcrhsodq_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> select error from v$datafile_header where file#=1015;

ERROR
-----------------------------------------------------------------
CANNOT OPEN FILE

SQL>

SQL> alter tablespace BACKUPOFBACKUP02 offline;

Tablespace altered.

SQL> select status from dba_tablespaces where tablespace_name='BACKUPOFBACKUP02';

STATUS
---------
READ ONLY

SQL>


RMAN> run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_21apr19_sbtch)';
restore tablespace BACKUPOFBACKUP02;
}2> 3> 4> 5>

using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=30 device type=SBT_TAPE
channel t1: WARNING: Oracle Test Disk API

Starting restore at 21-APR-19

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 01015 to /oradata_1/oratest/datafile/o1_mf_backupof_gcrhsodq_.dbf
channel t1: reading from backup piece backuptp_tbspc_ORATEST_108_1006193400_20190421
channel t1: piece handle=backuptp_tbspc_ORATEST_108_1006193400_20190421 tag=TAG20190421T180959
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
Finished restore at 21-APR-19
released channel: t1

RMAN>

SQL> select error from v$datafile_header where file#=1015;

ERROR
-----------------------------------------------------------------
OFFLINE NORMAL

SQL> alter tablespace BACKUPOFBACKUP02 online;

Tablespace altered.

SQL> select file_name from dba_data_files where lower(tablespace_name)='backupofbackup02';

FILE_NAME
--------------------------------------------------------------------------------
/oradata_1/oratest/datafile/o1_mf_backupof_gcrjv6tx_.dbf

SQL> select mdlvl,count(1)
from scott.test2
group by mdlvl;  2    3

     MDLVL   COUNT(1)
---------- ----------
         1        500
         0        499

SQL> select error from v$datafile_header where file#=1015;

ERROR
-----------------------------------------------------------------


SQL> select status from dba_tablespaces where tablespace_name='BACKUPOFBACKUP02';

STATUS
---------
READ ONLY

option disk backupset (using tag to pick the disk backup):

SQL> !ls -altr /oradata_1/oratest/datafile/o1_mf_backupof_gcrjv6tx_.dbf
-rw-rw----. 1 oracle oracle 10493952 Apr 21 18:18 /oradata_1/oratest/datafile/o1_mf_backupof_gcrjv6tx_.dbf

SQL> !rm /oradata_1/oratest/datafile/o1_mf_backupof_gcrjv6tx_.dbf

SQL> !ls -altr /oradata_1/oratest/datafile/o1_mf_backupof_gcrjv6tx_.dbf
ls: cannot access /oradata_1/oratest/datafile/o1_mf_backupof_gcrjv6tx_.dbf: No such file or directory

SQL> select error from v$datafile_header where file#=1015;

ERROR
-----------------------------------------------------------------
CANNOT OPEN FILE

SQL> select mdlvl,count(1)
from scott.test2
group by mdlvl;  2    3
select mdlvl,count(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 1015
ORA-01110: data file 1015:
'/oradata_1/oratest/datafile/o1_mf_backupof_gcrjv6tx_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

RMAN> run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP02 from tag TAG20190421T180721;
}2> 3> 4> 5>

using target database control file instead of recovery catalog
allocated channel: d1
channel d1: SID=14 device type=DISK

Starting restore at 21-APR-19

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 01015 to /oradata_1/oratest/datafile/o1_mf_backupof_gcrjv6tx_.dbf
channel d1: reading from backup piece /oradata_2/oratest_21apr19_dkch/backupdk_tbspc_ORATEST_106_1006193241_20190421
channel d1: piece handle=/oradata_2/oratest_21apr19_dkch/backupdk_tbspc_ORATEST_106_1006193241_20190421 tag=TAG20190421T180721
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:07
Finished restore at 21-APR-19
released channel: d1


SQL> select error from v$datafile_header where file#=1015;

ERROR
-----------------------------------------------------------------
OFFLINE NORMAL

SQL> alter tablespace BACKUPOFBACKUP02 online;

Tablespace altered.

SQL> select error from v$datafile_header where file#=1015;

ERROR
-----------------------------------------------------------------


SQL> select mdlvl,count(1)
from scott.test2
group by mdlvl;  2    3

     MDLVL   COUNT(1)
---------- ----------
         1        500
         0        499

SQL> select file_name from dba_data_files where lower(tablespace_name)='backupofbackup02';

FILE_NAME
--------------------------------------------------------------------------------
/oradata_1/oratest/datafile/o1_mf_backupof_gcrkb76p_.dbf

SQL>

option image copy:

run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP02 from tag TAG20190421T180458;
}


RMAN> run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP02 from tag TAG20190421T180458;
}2> 3> 4> 5>

using target database control file instead of recovery catalog
allocated channel: d1
channel d1: SID=268 device type=DISK

Starting restore at 21-APR-19

channel d1: restoring datafile 01015
input datafile copy RECID=2 STAMP=1006193100 file name=/oradata_2/oratest_21apr19/backup_tbspc_104_1006193098_20190421
destination for restore of datafile 01015: /oradata_1/oratest/datafile/o1_mf_backupof_gcrkb76p_.dbf
channel d1: copied datafile copy of datafile 01015
output file name=/oradata_1/oratest/datafile/o1_mf_backupof_gcrkks3n_.dbf RECID=0 STAMP=0
Finished restore at 21-APR-19
released channel: d1

RMAN> exit


SQL> select error from v$datafile_header where file#=1015;

ERROR
-----------------------------------------------------------------
OFFLINE NORMAL

SQL>
SQL> alter tablespace BACKUPOFBACKUP02 online;

Tablespace altered.

SQL> select error from v$datafile_header where file#=1015;

ERROR
-----------------------------------------------------------------


SQL> select mdlvl,count(1)
from scott.test2
group by mdlvl;  2    3

     MDLVL   COUNT(1)
---------- ----------
         1        500
         0        499

SQL>

SQL> select START_TIME,END_TIME,OPERATION,status,MBYTES_PROCESSED,INPUT_BYTES,OUTPUT_BYTES,OPTIMIZED,OUTPUT_DEVICE_TYPE
from v$rman_status
where END_TIME > sysdate -1/24 and operation!='LIST'
order by start_time;  2    3    4

START_TIME          END_TIME            OPERATION                         STATUS                  MBYTES_PROCESSED INPUT_BYTES OUTPUT_BYTES OPT OUTPUT_DEVICE_TYP
------------------- ------------------- --------------------------------- ----------------------- ---------------- ----------- ------------ --- -----------------
21/04/2019 18:04:05 21/04/2019 18:14:19 RMAN                              COMPLETED WITH ERRORS                148   151519232      152453120 NO
21/04/2019 18:04:57 21/04/2019 18:05:05 BACKUP                            COMPLETED                             31    31981568       32063488 NO  DISK  <<< IMG copy backup to disk
21/04/2019 18:05:02 21/04/2019 18:05:05 CONTROL FILE AND SPFILE AUTOBACK  COMPLETED                             21    21495808       21577728 NO  DISK
21/04/2019 18:07:21 21/04/2019 18:07:26 BACKUP                            COMPLETED                             23    23068672       22708224 NO  DISK  <<< backupset of image copy to disk
21/04/2019 18:07:23 21/04/2019 18:07:26 CONTROL FILE AND SPFILE AUTOBACK  COMPLETED                             21    21495808       21577728 NO  DISK
21/04/2019 18:09:59 21/04/2019 18:10:04 BACKUP                            COMPLETED                             31    31981568       29360128 NO  SBT_TAPE  <<< backupset of iamge copy to tape
21/04/2019 18:10:01 21/04/2019 18:10:04 CONTROL FILE AND SPFILE AUTOBACK  COMPLETED                             21    21495808       25165824 NO  SBT_TAPE
21/04/2019 18:17:38 21/04/2019 18:18:31 RMAN                              COMPLETED                             10           0       10485760 NO
21/04/2019 18:18:13 21/04/2019 18:18:15 RESTORE                           COMPLETED                             10           0       10485760 NO            <<< restore from sbt
21/04/2019 18:24:33 21/04/2019 18:24:35 RMAN                              COMPLETED                              0           0              0 NO
21/04/2019 18:24:38 21/04/2019 18:24:40 RMAN                              COMPLETED                              0           0              0 NO
21/04/2019 18:24:45 21/04/2019 18:25:31 RMAN                              COMPLETED                              0           0              0 NO
21/04/2019 18:25:41 21/04/2019 18:26:27 RMAN                              COMPLETED                             10     1130496       10485760 NO
21/04/2019 18:26:14 21/04/2019 18:26:22 RESTORE                           COMPLETED                             10     1130496       10485760 NO  DISK      <<< restore from disk backupset
21/04/2019 18:29:15 21/04/2019 18:30:26 RMAN                              COMPLETED                              0    10485760       10485760 NO
21/04/2019 18:30:16 21/04/2019 18:30:18 RESTORE                           COMPLETED                              0    10485760       10485760 NO  DISK      <<< restore from disk image copy

16 rows selected.

Summary: We could backup image copy as backupset to both disk and tape and also we are able to successfully perform restore as well.

Thank you for your visit, your comments are welcome.

Querying dba_hist_system_event to summarize the class based summarization of system events from awr data

Lab : Querying dba_hist_system_event to summarize the class based summarization of system events from awr data

Query:

with t as
(select INSTANCE_NUMBER
,snap_id
,WAIT_CLASS
,EVENT_NAME
,(total_waits - lag(total_waits,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) db_tot_waits
,(TOTAL_TIMEOUTS - lag(TOTAL_TIMEOUTS,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) db_tot_timeouts
,(TIME_WAITED_MICRO - lag(TIME_WAITED_MICRO,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) db_tot_timemic
,(TOTAL_WAITS_FG - lag(TOTAL_WAITS_FG,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) fg_tot_waits
,(TOTAL_TIMEOUTS_FG - lag(TOTAL_TIMEOUTS_FG,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) fg_tot_timeouts
,(TIME_WAITED_MICRO_FG - lag(TIME_WAITED_MICRO_FG,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) fg_tot_timemic
from DBA_HIST_SYSTEM_EVENT)
select a.END_INTERVAL_TIME tstmp
,a.instance_number in_nu
,t.wait_class
,sum(t.db_tot_timemic) class_agg_timemic
,sum(t.db_tot_timemic)/decode(sum(t.db_tot_waits),0,1,sum(t.db_tot_waits)) class_res_timemic
,round(sum(t.db_tot_timemic/decode(b.agg_db_tot_time,0,1,b.agg_db_tot_time))*100,2) pct_wt_class_time
,sum(t.db_tot_waits) class_agg_waits
,sum(db_tot_timeouts) class_agg_timeo
,round(sum(fg_tot_timemic)/decode(sum(t.db_tot_timemic),0,1,sum(t.db_tot_timemic))*100,2) pct_fg_class
from dba_hist_snapshot a
,t
,(select instance_number
,snap_id
,sum(db_tot_waits) agg_db_tot_wts
,sum(db_tot_timeouts) agg_db_tot_timout
,sum(db_tot_timemic) agg_db_tot_time
,sum(fg_tot_waits) agg_fg_tot_wts
,sum(fg_tot_timeouts) agg_fg_tot_timeo
,sum(fg_tot_timemic) agg_fg_tot_time
from t
group by instance_number
,snap_id) b
where a.snap_id=t.snap_id
and a.snap_id=b.snap_id
and a.INSTANCE_NUMBER=t.INSTANCE_NUMBER
and a.instance_number=b.instance_number
and a.BEGIN_INTERVAL_TIME > sysdate-2
group by a.END_INTERVAL_TIME
,a.instance_number
,t.wait_class
order by a.END_INTERVAL_TIME
,a.instance_number
,t.wait_class;

Result example:

TSTMP                         ,     IN_NU,WAIT_CLASS     ,CLASS_AGG_TIMEMIC,CLASS_RES_TIMEMIC,PCT_WT_CLASS_TIME,CLASS_AGG_WAITS,CLASS_AGG_TIMEO,PCT_FG_CLASS
------------------------------,----------,---------------,-----------------,-----------------,-----------------,---------------,---------------,------------
20-APR-19 04.27.03.450 PM     ,         1,Administrative ,            16321,            16321,                0,              1,        0,         100
20-APR-19 04.27.03.450 PM     ,         1,Application    ,          5658890,         -2829445,                0,             -2,        0,      102.78
20-APR-19 04.27.03.450 PM     ,         1,Commit         ,          2433284,       -110603.82,                0,            -22,        0,      130.45
20-APR-19 04.27.03.450 PM     ,         1,Concurrency    ,          7756959,       -13080.875,                0,           -593,        0,        81.8
20-APR-19 04.27.03.450 PM     ,         1,Configuration  ,         15554245,       5184748.33,                0,              3,        2,         100
20-APR-19 04.27.03.450 PM     ,         1,Idle           ,       -6.258E+11,        1929801.8,            99.98,        -324287,  -126260,         .05
20-APR-19 04.27.03.450 PM     ,         1,Network        ,             3885,       13.7765957,                0,            282,        0,       82.57
20-APR-19 04.27.03.450 PM     ,         1,Other          ,          9562378,       -133.03623,                0,         -71878,    -7152,      143.58
20-APR-19 04.27.03.450 PM     ,         1,Scheduler      ,         14416183,        -30672.73,                0,           -470,        0,       98.34
20-APR-19 04.27.03.450 PM     ,         1,System I/O     ,       -332436358,       5973.91385,              .05,         -55648,        0,       -6.51
20-APR-19 04.27.03.450 PM     ,         1,User I/O       ,        171322583,       -264795.34,             -.03,           -647,        0,       67.25
20-APR-19 05.00.15.137 PM     ,         1,Administrative ,           184875,          9243.75,                0,             20,        0,       -8.83
20-APR-19 05.00.15.137 PM     ,         1,Application    ,            11573,       136.152941,                0,             85,        0,       90.27
20-APR-19 05.00.15.137 PM     ,         1,Commit         ,           213992,          42798.4,                0,              5,        0,           0
20-APR-19 05.00.15.137 PM     ,         1,Concurrency    ,           257055,       3338.37662,                0,             77,        0,       97.82
20-APR-19 05.00.15.137 PM     ,         1,Configuration  ,                0,                0,                0,              0,        0,           0
20-APR-19 05.00.15.137 PM     ,         1,Idle           ,       8.7817E+10,        1917102.4,             99.9,          45807,    15792,        1.59
20-APR-19 05.00.15.137 PM     ,         1,Network        ,             9689,       15.8316993,                0,            612,        0,        79.7
20-APR-19 05.00.15.137 PM     ,         1,Other          ,         12605424,       1320.76949,              .01,           9544,      631,       32.46
20-APR-19 05.00.15.137 PM     ,         1,Scheduler      ,           135399,       1289.51429,                0,            105,        0,         100
20-APR-19 05.00.15.137 PM     ,         1,System I/O     ,         68315547,       5247.37284,              .08,          13019,        0,        1.77
20-APR-19 05.00.15.137 PM     ,         1,User I/O       ,          8369085,        4197.1339,              .01,           1994,        0,        1.97
20-APR-19 06.00.40.902 PM     ,         1,Administrative ,                0,                0,                0,              0,        0,           0
20-APR-19 06.00.40.902 PM     ,         1,Application    ,                0,                0,                0,              0,        0,           0
20-APR-19 06.00.40.902 PM     ,         1,Commit         ,          1673828,       139485.667,                0,             12,        0,       29.57
20-APR-19 06.00.40.902 PM     ,         1,Concurrency    ,            87558,       587.637584,                0,            149,        0,       85.15
20-APR-19 06.00.40.902 PM     ,         1,Configuration  ,                0,                0,                0,              0,        0,           0
...
21-APR-19 04.00.59.614 PM     ,         1,Idle           ,       1.5709E+11,       1906963.95,            99.91,          82376,    29052,        2.75
21-APR-19 04.00.59.614 PM     ,         1,Network        ,             2272,       23.9157895,                0,             95,        0,         100
21-APR-19 04.00.59.614 PM     ,         1,Other          ,         13535952,       756.113954,              .01,          17902,     2075,       29.28
21-APR-19 04.00.59.614 PM     ,         1,Scheduler      ,           213924,       1258.37647,                0,            170,        0,         100
21-APR-19 04.00.59.614 PM     ,         1,System I/O     ,        129506987,       9125.99443,              .08,          14191,        0,           0
21-APR-19 04.00.59.614 PM     ,         1,User I/O       ,          2095721,       1806.65603,                0,           1160,        0,        5.24
21-APR-19 05.00.20.950 PM     ,         1,Application    ,                0,                0,                0,              0,        0,           0
21-APR-19 05.00.20.950 PM     ,         1,Commit         ,          3888804,         388880.4,                0,             10,        0,       92.64
21-APR-19 05.00.20.950 PM     ,         1,Concurrency    ,           122897,       967.692913,                0,            127,        0,       91.07
21-APR-19 05.00.20.950 PM     ,         1,Configuration  ,                0,                0,                0,              0,        0,           0
21-APR-19 05.00.20.950 PM     ,         1,Idle           ,       1.5227E+11,        1915807.4,            99.87,          79481,    28541,         .13
21-APR-19 05.00.20.950 PM     ,         1,Network        ,                0,                0,                0,              0,        0,           0
21-APR-19 05.00.20.950 PM     ,         1,Other          ,         17030382,       1002.90807,              .01,          16981,     1822,       23.76
21-APR-19 05.00.20.950 PM     ,         1,Scheduler      ,           295912,       2026.79452,                0,            146,        0,       77.19
21-APR-19 05.00.20.950 PM     ,         1,System I/O     ,        177589588,       12890.2945,              .12,          13777,        0,           0
21-APR-19 05.00.20.950 PM     ,         1,User I/O       ,          1434472,       1941.09878,                0,            739,        0,           0

160 rows selected.