Rman archivelog delete

list archivelog all;
CROSSCHECK ARCHIVELOG ALL;

delete noprompt expired archivelog all;

Dumping redo log file information – Oracle Database

Dumping redo log file information – Oracle Database 10g

Published on January 2, 2008

Redo log files are written by redo log writter process. The files are written in Oracle proprietary format and cannot be read directly. However there are simple command for reading the logfile. I had a situation where I wanted to read the blocks of online redo log files. I was knowing the commands to read datafile blocks by giving the file number and block numbers, but it took me lot of time to actually search the command for redo log files.
Anyway after speding some time, I come to know some of the ways we can dump the content of redo log files.
We basically dump the output of redo log files in a trace and then read the trace file to understand the content. Below are some of the useful command.
The following ways of dumping a redo log file are covered
1. To dump records based in DBA (Data Block Address)
2. To dump records based on RBA (Redo Block Address)
3. To dump records based on SCN
4. To dump records based on time
5. Dump the file header information
6. Dump an entire log file
1. To dump records based on DBA  (Data Block Address)
Connect to database using sysdba and execute the below command
ALTER SYSTEM DUMP LOGFILE ‘filename’  DBA MIN (fileno) (blockno) DBA MAX (fileno) (blockno);
Example:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’ DBA MIN 5 . 31125 DBA MAX 5 . 31150;
This will cause all the changes to the specified range of data blocks to be dumped to the trace file.  In the example given, all redo records for file #5, blocks 31125 thru 31150 are dumped.
2. To dump records based on RBA (Redo Block Address)
This will dump all redo records for the range of redo addresses specified for the given sequence number and block number.
Syntax:
ALTER SYSTEM DUMP LOGFILE ‘filename’ RBA MIN seqno blockno RBA MAX seqno blockno;
Example:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’ RBA MIN 2050 13255 RBA MAX 2255 15555;
3. To dump records based on SCN
Using this option will cause redo records owning changes within the SCN range
specified to be dumped to the trace file.
ALTER SYSTEM DUMP LOGFILE ‘filename’ SCN MIN minscn SCN MAX maxscn;
Example:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’ SCN MIN 103243  SCN MAX 103294;
4. To dump records based on time
Using this option will cause redo records created within the time range specified to be dumped to the trace file.
ALTER SYSTEM DUMP LOGFILE ‘filename’ TIME MIN value TIME MAX value;
Example:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’ TIME MIN 299425687 TIME MAX 299458800;
Please Note: the time value is given in REDO DUMP TIME
5. Dump the file header information
This will dump file header information for every online redo log file.
alter session set events ‘immediate trace name redohdr level 10’;
6. Dump an entire log file:
ALTER SYSTEM DUMP LOGFILE ‘filename’;
Please note: Fully qualify the filename, and include the single quotes.
Example:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’;
References:
http://yumianfeilong.com/2007/04/02/how-to-dump-redo-log-file-information/

Howto Access a Linux Machine Behind a Home Router With SSH Tunnels

https://juliansimioni.com/blog/howto-access-a-linux-machine-behind-a-home-router-with-ssh-tunnels/

Recently, I had the need to set up a Linux machine so that I could put it behind a home router with no special configuration, and still access the machine from anywhere. Machines behind home routers are normally difficult to access from the outside world for two reasons: dynamic IP addresses and network address translation.
Dynamic IPs mean you don’t know, without being behind the same home router, what the IP address to connect to is at any given time. Network Address Translation makes it impossible to connect to a machine behind a home router unless the router is specifically configured to allow it.
However, nothing about this arrangement disallows the machine behind the home router from making outbound connections, and thanks to the flexibility of SSH tunnels, we can use this to create a simple, self configuring and healing connection allowing us to SSH into the machine behind the home router.
Here’s the different pieces of hardware that play into the story:
HostA: This is a Linux machine with a known and publicly accessible IP address. Its best if the IP address is static. In my case it’s a VPS on Linode, but any Linux machine accessible via SSH can work. No root access or special configurations are required.
Router: This is the home router. For our purposes it’s just a standard Linksys router with no special configuration, and we do not have access to its control panel.
HostB: This is the Linux machine you will put behind the home router. Again, full root access is not required, only a user account. It’s assumed that we will lose physical access to this machine once its placed behind the home router, but can configure it as we wish before then.
HostC: another Linux machine of any type and network location, where we have direct physical access.
Given these machines, the requirement is simple: allow us to SSH from HostC to HostB.

SSH Tunnel Primer

One of the most powerful abilities of SSH is to set up port forwarding (much like might be done on a home router), but ensure the forwarded traffic is routed through a secure connection created by SSH.
One of the classic uses for SSH tunnels is in fact exactly what we need to get around the restrictions put in place by the router. If we execute the following command on HostB

ssh -R 10022:localhost:22 HostA

then we can connect to HostB from HostA this way:

ssh -p 10022 localhost

How does this work? The first command tells SSH to set up a secure port forwarding from port 22, where SSH is listening, on HostB, to port 10022 on HostA. The router does not disallow HostB from initiating outbound connections, so this command does not fail. Users other than root can set up sockets on ports above 1024 without issue, so this also doesn’t require any special permissions on HostA.
However, currently, this command would have to be run manually on HostB, and it simply drops us into an SSH session to HostA. As soon as that session was closed, or as soon as there was a network issue causing the SSH session to terminate, our tunnel to HostB terminates as well. A little more work is required to create a foolproof setup.

Setting up a hands-free SSH tunnel

To facilitate setting up persistent port forwarding, SSH has two handy options: -N, and -f. Combining these two with our command above will send SSH into the background after initializing our port forwarding. With this, a simple cron job can ensure that we have a connection to HostB even if the network temporarily goes down or HostB is rebooted. However, it will create a new SSH connection every time, which is at best inefficient, and at worst can consume all the resources on one of our machines.
How then, can we check if there is already a connection from HostB to HostA, and only initialize a connection when needed? Let’s take a look at another SSH port forwarding command:

ssh -L 19922:HostA:22 HostA

If run from HostB, this will set up a port forwarding from port 19922 on HostB, to port 22 on HostA, essentially the reverse of the previous forwarding. We can already ssh to port 22 on HostA directly from HostB, but now, we can also SSH to HostA from HostB with the following command:

ssh localhost -p 19922

Since port 19922 is only active when our ssh tunnel is in place, this allows us to check for the tunnel’s existence: if our tunnel is down, SSH will fail with a connection error.
One final trick SSH gives us is the ability to set up multiple port forwardings in one command. With that, our complete solution can be written in a simple bash script:

1 2 3 4 5 6 7 8 9 10 11 12 13

createTunnel() { /usr/bin/ssh -f -N -R 10022:localhost:22 -L19922:HostA:22 HostA if [[ $? -eq 0 ]]; then echo Tunnel to HostA created successfully else echo An error occurred creating a tunnel to HostA. Return code: $? fi } /usr/bin/ssh -p 19922 localhost ls > /dev/null if [[ $? -ne 0 ]]; then echo Creating new tunnel connection to HostA createTunnel fi

The meat of the script is in the createTunnel function, which creates the tunnel as described above. The canary tunnel is checked on line 9, and if the connection fails, a new tunnel is created.
Run this code in a cron job every few minutes, and that will take care of keeping the tunnel in place at all times.

Connecting

Now that HostB has the createTunnel code running periodically, it’s time to use it!
The simplest way is to simply chain multiple ssh commands together, as follows:

1	ssh -t HostA 'ssh localhost -p 10022'

The -t parameter is needed to allocate a pseudo-TTY to be allocated. Without it there won’t be any output.
Better still, if you install netcat on HostA, is to set up a ProxyCommand in your SSH config:

1 2 3 4

# ~/.ssh/config Host HostB ProxyCommand ssh -q HostA nc -q0 localhost 10022

Now you can simply run ssh HostB directly¹.
I’ve been running this code myself for several months and haven’t had any problems, but would love to hear from anyone else using something similar. Happy coding!

Find running SQLs and Kill Session

==========find running SQLs
col USERNAME for a20
col SQL_TEXT for a100
col SID for 999999
set lines 300

select sesion.sid,
sesion.serial#,
sesion.username,
sesion.sql_id,
sesion.sql_child_number,
optimizer_mode,
hash_value,
address,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null;


==========find locking sessions

SELECT vh.sid locking_sid,
 vs.status status,
 vs.program program_holding,
 vw.sid waiter_sid,
 vsw.program program_waiting
FROM v$lock vh,
 v$lock vw,
 v$session vs,
 v$session vsw
WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2
 FROM v$lock
 WHERE request = 0
 INTERSECT
 SELECT id1, id2
 FROM v$lock
 WHERE lmode = 0)
 AND vh.id1 = vw.id1
 AND vh.id2 = vw.id2
 AND vh.request = 0
 AND vw.lmode = 0
 AND vh.sid = vs.sid
 AND vw.sid = vsw.sid;




 SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL;

 

==========Find Long SQLsselect v$session_longops.time_remaining, v$session_longops.opname, v$session_longops.start_time,
v$session_longops.last_update_time, v$session_longops.elapsed_seconds, ((v$session_longops.elapsed_seconds /
(v$session_longops.elapsed_seconds + v$session_longops.time_remaining))*100) pct_complete,
V$SQL.SQL_TEXT from v$session_longops, V$SQL where v$session_longops.SQL_ADDRESS=V$SQL.ADDRESS
and v$session_longops.time_remaining <> 0
order by V$SQL.SQL_TEXT, v$session_longops.last_update_time;


==========Kill
alter system kill session '929,23760' IMMEDIATE;
 
==========find pid
select     s.username
,     s.sid
,     s.serial#
,     p.spid
,     last_call_et
,     status
from      V$SESSION s
,     V$PROCESS p
where     s.PADDR = p.ADDR
and     s.sid= 929




==========another way to find sql

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SID,   
       MACHINE, 
       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT, 
      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT 
  FROM V$SESSION SES,   
       V$SQLtext_with_newlines SQL 
 where SES.STATUS = 'ACTIVE'
   and SES.USERNAME is not null
   and SES.SQL_ADDRESS    = SQL.ADDRESS 
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE 
   and Ses.AUDSID <> userenv('SESSIONID') 
 order by runt desc, 1,sql.piece;

  

Finding a locking session

How to identify lockers

This article will explain about locks on rows and on objects in ORACLE.

Locks on rows can cause performance problems or even impede a transaction from finishing, when there are processes running for long time we need to validate that they are not waiting on a row(s).

When there is a lock on a row there is also a lock on the dependent objects, if we want to perform a DDL on a locked object we will get an ORA-00054 error.

Scenario 1:

Terminal A is locking a row and Terminal B is waiting on it:

–TERMINAL A

1 2 3 4 5 6

SQL> update map1 set col2='MYLOCK' where col1=300;   1 row updated.   SQL> (..no commit here..)

–TERMINAL B

1 2	SQL> update map1 set col2='NEWVAL2' where col1=300; (..waiting..)

Now, lets create a session as a DBA User to monitor the system, this query will tell the locking and waiting SIDs.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SELECT vh.sid locking_sid,  vs.status status,  vs.program program_holding,  vw.sid waiter_sid,  vsw.program program_waiting FROM v$lock vh,  v$lock vw,  v$session vs,  v$session vsw WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2  FROM v$lock  WHERE request = 0  INTERSECT  SELECT id1, id2  FROM v$lock  WHERE lmode = 0)  AND vh.id1 = vw.id1  AND vh.id2 = vw.id2  AND vh.request = 0  AND vw.lmode = 0  AND vh.sid = vs.sid  AND vw.sid = vsw.sid;

LOCKING_SID STATUS   PROGRAM_HOLDING                WAITER_SID PROGRAM_WAITING
----------- -------- ------------------------------ ---------- ------------------------------
 144        ACTIVE   sqlplus@rh4_node1.fadeserver.n        131 sqlplus@rh4_node1.fadeserver.n
                     et (TNS V1-V3)                            et (TNS V1-V3)

Here is an expanded version of the same query, it also includes jobs information.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

SELECT vs.username,  vs.osuser,  vh.sid locking_sid,  vs.status status,  vs.module module,  vs.program program_holding,  jrh.job_name,  vsw.username,  vsw.osuser,  vw.sid waiter_sid,  vsw.program program_waiting,  jrw.job_name,  'alter system kill session ' || ''''|| vh.sid || ',' || vs.serial# || ''';'  "Kill_Command" FROM v$lock vh,  v$lock vw,  v$session vs,  v$session vsw,  dba_scheduler_running_jobs jrh,  dba_scheduler_running_jobs jrw WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2  FROM v$lock  WHERE request = 0  INTERSECT  SELECT id1, id2  FROM v$lock  WHERE lmode = 0)  AND vh.id1 = vw.id1  AND vh.id2 = vw.id2  AND vh.request = 0  AND vw.lmode = 0  AND vh.sid = vs.sid  AND vw.sid = vsw.sid  AND vh.sid = jrh.session_id(+)  AND vw.sid = jrw.session_id(+);

USERNAME OSUSER  LOCKING_SID STATUS   MODULE  PROGRAM_HO JOB_N USERNAME OSUSER  WAITER_SID PROGRAM_WA JOB_N Kill_
-------- ------- ----------- -------- ------- ---------- ----- -------- ------- ---------- ---------- ----- -----
CACOSTA  oracle          144 ACTIVE   SQL*Plu sqlplus@rh       CACOSTA  oracle         131 sqlplus@rh       alter
                                      s       4_node1.fa                                   4_node1.fa        syst
                                              deserver.n                                   deserver.n       em ki
                                              et (TNS V1                                   et (TNS V1       ll se
                                              -V3)                                         -V3)             ssion
                                                                                                            '144
                                                                                                            ,3897
                                                                                                            3';

We can see that the user CACOSTA, sid 144 is locking the session 131.

Scenario 2:
We are performing a DDL (alter somehow the object) and we get an ORA-00054 error.

I have canceled the waiting session in the example above and now I’m creating an index on the table:

1 2 3 4 5

SQL> create index ind2 on map1(col2); create index ind2 on map1(col2)  * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified

If I re-run the query fromt he previous scenario it won’t return any rows, because there are no waiting sessions (I canceled the waiting update).

First we need to find out the object ID:

1 2 3 4 5 6 7

SQL> select object_id from dba_objects  2  where owner='CACOSTA'  3  and object_name='MAP1';    OBJECT_ID ----------  52255

Now lets see who is blocking the object 52255

1 2 3 4 5 6 7 8 9 10 11

SELECT c.owner,  c.object_name,  c.object_type,  b.sid,  b.serial#,  b.status,  b.osuser,  b.machine FROM v$locked_object a, v$session b, dba_objects c WHERE b.sid = a.session_id AND a.object_id = c.object_id and a.object_id=52255;

OWNER    OBJECT_NAME   OBJECT_TYPE                SID    SERIAL# STATUS   OSUSER  MACHINE
-------- ------------- ------------------- ---------- ---------- -------- ------- ---------------
CACOSTA  MAP1          TABLE                      144      38973 ACTIVE   oracle  rh4_node1.fades
                                                                                  erver.net

Good luck!