STEPS TO TAKE BACKUP ON TAPE

[root@iptvdb2]mt -f /dev/rmt/0n status
Vendor 'HP      ' Product 'Ultrium 4-SCSI ' tape drive:
   sense key(0x0)= No Additional Sense   residual= 0   retries= 0
   file no= 2   block no= 0
[root@iptvdb2]


[root@iptvdb2]tar -cvf /dev/rmt/0n /nasbackup/RMANBACKUP/Nov_04_2013_backup/

a /nasbackup/RMANBACKUP/Nov_04_2013_backup// 0 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_10_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_11_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_12_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_13_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_14_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_15_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_16_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_17_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_18_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_19_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_1_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_20_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_21_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_22_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_23_1.bak 1080960 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_2_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_3_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_4_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_5_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_6_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_7_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_8_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j6oo3v2s_9_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j7oo42i2_1_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j7oo42i2_2_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j7oo42i2_3_1.bak 5101856 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j8oo42u3_1_1.bak 10240000 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j8oo42u3_2_1.bak 1047248 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_j9oo4341_1_1.bak 9100064 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//weekly_ORCL_jaoo4396_1_1.bak 6004224 tape blocks
a /nasbackup/RMANBACKUP/Nov_04_2013_backup//archive_d_jboo43de_1_1.bak 5870552 tape blocks
[root@iptvdb2]
[root@iptvdb2]




SOME OTHER ATTRIBUTES

# mt status    Print status information about the tape unit.

  # mt rewind    Rewind the tape.

  # mt erase     Erase the tape.

  # mt retension Re-tension the tape (one full wind forth and back.

  # mt fsf 1     Forward space count by one file. One can be any number.

  -f option can be used with mt to specify the different device. For
  solaris /dev/rmt/0 is the default device.

  # mt -f /dev/rmt/1n fsf 3

ERROR NO SPACE LEFT ON DEVICE

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed withstatus: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper

Although theerror says “No space left on device”, but it is not true. The problem is duekernel semaphore configuration. After setting a proper value(echo"250 1500 100 128" > /proc/sys/kernel/sem), the problemwill be solved:

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on MonJun 21 08:20:51 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-27154: post/wait create failed
ORA-27300: OS system dependentoperation:semget failed with status: 28
ORA-27301: OS failure message: No spaceleft on device
ORA-27302: failure occurred at:sskgpsemsper
SQL> exit
Disconnected
[oracle@localhost ~]$ df -kh
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
304G 173G 115G 61% /
/dev/hda1 99M 12M 82M 13% /boot
tmpfs 1.3G 0 1.3G 0% /dev/shm
[root@localhost kernel]# cat /proc/sys/kernel/sem
250 100 32 128
[root@localhost kernel]# echo "250 1500 100 128" > /proc/sys/kernel/sem
[root@localhost kernel]# cat /proc/sys/kernel/sem
250 500 100 128
[root@localhost kernel]#

SCRIPT FOR CHECKING ARCHIVE LOG GENERATION ON ORACLE DATABASE

/////////////////////////On daily basis:

set pagesize 100
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
-- As Table
SELECT TRUNC(completion_time) AS time, ROUND(SUM(blocks * block_size)/1024/1024,2) AS size_mb, ROUND(SUM(blocks * block_size)/1024/1024/1024,2) AS size_gb
FROM v$archived_log GROUP BY TRUNC(completion_time) ORDER BY 1 DESC;
-- As csv-File
SELECT TRUNC(completion_time) || ';' || ROUND(SUM(blocks * block_size)/1024/1024,2) || ';' || ROUND(SUM(blocks * block_size)/1024/1024/1024,2)
FROM v$archived_log GROUP BY TRUNC(completion_time) ORDER BY 1 DESC;



//////////////////////////On hourly basis:



set pagesize 100
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24';
-- As Table
SELECT TRUNC(completion_time,'HH24') AS time, ROUND(SUM(blocks * block_size)/1024/1024,2) AS size_mb, ROUND(SUM(blocks * block_size)/1024/1024/1024,2) AS size_gb
FROM v$archived_log GROUP BY TRUNC(completion_time,'HH24') ORDER BY 1 DESC;
-- As csv-File
SELECT TRUNC(completion_time,'HH24') || ';' || ROUND(SUM(blocks * block_size)/1024/1024,2) || ';' || ROUND(SUM(blocks * block_size)/1024/1024/1024,2)
FROM v$archived_log GROUP BY TRUNC(completion_time,'HH24') ORDER BY 1 DESC;

SCRIPT TO CALCULATE UNDO SIZE AND RETENTION TIME IN ORACLE DATABASE FOR EXP-00008: ORACLE error 1555 encountered ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small error

//////////////////////maximum time take to execute query in sec


SQL>select max(maxquerylen) from v$undostat;


//////////////////////////Needed UNDO Size in MB


SQL>SELECT
ROUND(d.undo_size/(1024*1024),2) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((TO_NUMBER(e.value)*TO_NUMBER(f.value)*g.undo_block_per_sec)/(1024*1024),2) "NEEDED UNDO SIZE [MByte]"
FROM
(
SELECT
SUM(a.bytes) undo_size
FROM
v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE
c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.TS# = b.TS#
) d,
v$parameter e,
v$parameter f,
(
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM
v$undostat
) g
WHERE
e.name = 'undo_retention'
AND f.name = 'db_block_size';


///////////////////////Optimal UNDO Retention in seconds





SQL>SELECT
ROUND(d.undo_size/(1024*1024),2) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size/(TO_NUMBER(f.value)*g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM
(
SELECT
SUM(a.bytes) undo_size
FROM
v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE
c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.TS# = b.TS#
) d,
v$parameter e,
v$parameter f,
(
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM
v$undostat
) g
WHERE
e.name = 'undo_retention'
AND f.name = 'db_block_size';


/////////////////////for change undo retention time



SQL>ALTER SYSTEM SET UNDO_RETENTION = [values based on your calculation];


/////////////////////////////number of error ora-01555



SQL>select 'Number of "ORA-01555 (Snapshot too old)" encountered since
the last startup of the instance : ' || sum(ssolderrcnt)
from v$undostat;




/////////////////////Actual undo tablespace size


SQL>SELECT SUM(a.bytes/1024/1024) undo_size
  FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;