ZFS snapshots (Overview)

A snapshot is a read-only copy of a file system or volume. Snapshots can be created almost instantly, and they initially consume no additional disk space within the pool. It’s important for the Oracle DBA in Montreal to have a list of these commands.

How can I create a new ZFS snapshot?

$sudo zfs snapshot rpool/DBORA/u01@u01snapshop

How can I list all ZFS files system and snapshots (in Bold)?

$ zfs list -r -t all

Or

$ zfs list -o space -r rpool

How can I know the creation date of the ZFS snapshots?

$ zfs list -r -t snapshot -o name,creation

How can I delete a ZFS snapshop?

$ sudo zfs destroy rpool/DBORA/u01@now

How can I use my ZFS snapshop to restore a single file?

I want to restore the file /u01/test.sh

$cd /u01
$cd .zfs/snapshot/u01snapshop/
$cp test.sh /u01/

or to restore a copie without replace current one :

$cp test.sh /u01/test2.sh

You can use a snapshot like any other (read-only) FS to move files or folder to another place.

How can I use my ZFS snapshop to restore a File system?

This command will discard all changes made to the file system since the snapshot was created:

$sudo zfs rollback rpool/DBORA/u01@u01snapshop
cannot rollback to ‘rpool/DBORA/u01@u01snapshop’: more recent snapshots exist
use ‘-r’ to force deletion of the following snapshots:
rpool/DBORA/u01@u01snapshop2
rpool/DBORA/u01@u01snapshop3

$sudo zfs rollback -r rpool/DBORA/u01@u01snapshop


More informations in the Oracle documentation.


Vincent Fenoll DBA Oracle Montreal

SQL Informations

The Oracle database administrator knows the pid of an Oracle resource-intensive process. Now we want to gather some informations on the SQL Statement (SQL_ID, username, program, terminal…) before running an explain plan.

SELECT    'USERNAME : ' || s.username  || CHR (10)   
		|| 'SCHEMA : '  || s.schemaname || CHR (10) 
		|| 'OSUSER : '  || s.osuser    || CHR (10)  
		|| 'PROGRAM : ' || s.program   || CHR (10)  
		|| 'MACHINE : ' || s.machine   || CHR (10)  
		|| 'TERMINAL : ' || s.terminal  || CHR (10)  
		|| 'SPID : '    || p.spid      || CHR (10)  
		|| 'SID : '     || s.sid       || CHR (10)  
		|| 'SERIAL# : ' || s.serial#   || CHR (10)  
		|| 'TYPE : '    || s.TYPE      || CHR (10)  
		|| 'SQL ID : '  || q.sql_id    || CHR (10)  
		|| 'CHILD_NUMBER : '  || q.child_number    || CHR (10)  
		|| 'SQL TEXT : ' || q.sql_text 
          RESULT
  FROM v$session s, v$process p, v$sql q
 WHERE s.paddr = p.addr AND s.sql_id = q.sql_id(+) AND p.spid = '&&MY_PID';
 

To find the cpu intensive queries:
http://www.oracle-scripts.net/cpu-issues/

To display the Oracle explain plan:
http://www.oracle-scripts.net/display-cursor/

Vincent Fenoll – Montreal

I/O Bottlenecks

Our Oracle databases in Montreal are sometimes experiencing performance problems and the Oracle DBA wants to know if there are I/O Bottlenecks.

 

If working with a zone (a virtual operating system abstraction), first you will have to find the disk:

$ zpool status
  pool: rpool
 state: ONLINE
  scan: none requested
config:

        NAME    STATE     READ WRITE CKSUM
        rpool   ONLINE       0     0     0
          c1d6  ONLINE       0     0     0		<--- keep"d6"

$  iostat -xd 5

r/s:  Reads/second
w/s:  Writes/second
kr/s: kilobytes read per second
kw/s: kilobytes written per second
wait: average number of transactions waiting for service (queue length)
actv: average number of transactions actively being serviced
svc_t:average response time  of  transactions,  in  milliseconds
%w:   percent of time there are transactions waiting for service
%b:  percent of time the disk is busy

                  extended device statistics
device      r/s    w/s   kr/s   kw/s wait actv  svc_t  %w  %b
1/md1      56.9    0.0 2104.5    0.0  0.0  0.4    7.9   0  32
1/md0      56.9    0.0 2104.5    0.0  0.0  0.4    7.9   0  32
1/md6      56.9    0.0 2104.5    0.0  0.0  0.4    7.9   0  32
2/md1       0.0   10.8    0.0   86.6  0.0  0.2   16.3   0  18
2/md0       0.0   10.8    0.0   86.6  0.0  0.3   28.1   0  18
2/md210     0.0    0.2    0.0    1.6  0.0  0.0    4.2   0   0

If working with another type of virtual zone, you will have difficulties to find which disks are experiencing high I/O activity.

But hopefully, you can have some help with SQL*Plus:

(1) Look for sessions that are currently waiting for I/O resources:
SELECT username,
       program,
       machine,
       sql_id
  FROM v$session
 WHERE event LIKE 'db file%read';

(2) which SQL statements are using a lots of disks:

 col schema format a20
 SELECT *
  FROM (  SELECT parsing_schema_name Schema, SQL_ID,
                 SUBSTR (sql_text, 1, 75) SQL,
                 disk_reads
            FROM v$sql
        ORDER BY disk_reads DESC)
 WHERE ROWNUM < 20;

 And with the result set of (1) or (2):
 set long 1000
 select SQL_FULLTEXT from v$sql where sql_id='xxxxxxxxxxx'

--> Tune your SQL Statement or execute it less times.

After that you can also check the global Oracle wait events:
http://www.oracle-scripts.net/resources-waits/

CPU Issues

The DBAs want to know if there are performance issues with cpu in an Oracle database for example:


First:
$ ps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head
%CPU    PID     USER TT      COMMAND
 55.3 934757   oracle ?       oracleGdefgP (LOCAL=NO)
 10.1 935480   oracle ?       oracleGdefgP (LOCAL=NO)
 0.1 935247   oracle ?       oracleGdefgP (LOCAL=NO)
 0.1 935041   oracle ?       oracleGdefgP (LOCAL=NO)

(you can make an alias with this command)

Linux or other UNIX:
$ top
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14848 oracle 25 0 188m 13m 15m R 100.1 0.2 33661:45 oracle
14853 oracle 25 0 188m 13m 15m R 99.8 0.2 313225:41 oracle
9725 oracle 18 0 1045m 287m 150m R 58.6 3.8 0:41.89 oracle

$ ps -ef | grep 14848
--> Tune application or SQL


Solaris:
$ prstat
   PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
180478 oracle   1239M 1230M sleep   59    0  10:24:37 100.0% oracle/1
180500 oracle   1245M 1235M sleep   59    0   4:34:17 20.0% oracle/11
180502 oracle   1234M 1224M sleep   59    0   2:30:03 0.0% oracle/1

$ ps -ef | grep 180478
--> Tune application or SQL

Vincent Fenoll – Oracle DBA in Montreal (QC) Canada

Disk Space Full

When a disk is out os space, the database hungs or is unreachable and the Oracle DBA wants to find where are the large files to move or delete them. For example:


$ df –h
--> show 100% used

-- Find first 15 large files:
$ cd "My file system full"
$ find . -ls | sort -nrk7 | head -15
montreal.zip
quebec.zip
laval.zip
saint-eustache.zip
oracle-dba-montreal.zip
results.zip

-- Find first 15 large folders on Solaris (useful for small but big amount of files )
$ du -o . | sort -nr | head -15
(other O/S replace -o by -S)

Author: Vincent Fenoll – Montreal