Database Status
From dbawiki
Contents
- 1 Check datafiles for their recovery status (compared with what is in the controlfile)
- 2 How to stop and start a database on a Solaris Veritas cluster
- 3 Following a database crash...
- 4 How big is the database?
- 5 How big are the tablespaces?
- 6 Database Free space report
- 7 Which schemas are taking up all of the space?
- 8 Show the ten largest objects in the database
- 9 Is java installed in the database?
- 10 Show character set information
- 11 Show all used features
- 12 Move a database from one host to another without RMAN
- 12.1 Get a text version of the controlfile
- 12.2 Grab a copy of the pfile
- 12.3 Send these 2 files to the destination server
- 12.4 Shutdown the database
- 12.5 Copy the database datafiles to the new host (see in the controlfile creation script where they are)
- 12.6 Edit the files
- 12.7 Add database entry to /etc/oratab
- 12.8 Create or add entry to listener.ora
- 12.9 Create or add entry to tnsnames.ora
- 12.10 Setup environment for database
- 12.11 Copy init file to where it can be used
- 12.12 Create the new controlfile
- 12.13 Mount the database
- 12.14 Open the database
- 12.15 Check the database
Check datafiles for their recovery status (compared with what is in the controlfile)[edit]
Depending on how the database was last shut down (or recovered), this will show if data files are to of sync with the control file.
set lines 1000 pages 100 numwidth 15
col name for a60
col stats for a6
col datafile_status for a18
select
a.name
,a.status
,a.file#
,a.checkpoint_change# control_file_SCN
,b.checkpoint_change# datafile_SCN
,case
when ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'
when ((b.checkpoint_change#) = 0) THEN 'File Missing?'
when ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Rec. Req.'
when ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'
else 'what the ?'
end datafile_status
from v$datafile a -- control file SCN for datafile
,v$datafile_header b -- datafile header SCN
where a.file# = b.file#
order by a.file#
/
How to stop and start a database on a Solaris Veritas cluster[edit]
Using command line...
Cluster logfile /var/VRTSvcs/log/engine_A.log Cluster status hastatus -sum Clear failed resource hares -clear $SID_ORA -sys $host Start database hares -online $SID_ORA -sys $host Start listener hares -online $SID_LSNR -sys $host
or using a script...
cat cluster_database.ksh start|stop <SID>
#!/bin/ksh
#set -x
database=$2
PATH=$PATH:/opt/VRTSvcs/bin
export PATH
DATUM=`date +%Y%m%d`
LOGFILE="/oracle/logs/cluster_scripts.${DATUM}.log"
typeset -i count
typeset -i seconds
count=1
seconds=0
echo "`date +%Y%m%d-%H%M`: we will $1 database ${database}" | tee -a ${LOGFILE}
#########################################################################
# Run this script as root to stop/start Oracle database incl. listeners #
#########################################################################
#
case $1 in
start)
#system=`hares -value ${database}_ORA LastOnline`
#if [ -z "${system}" ]
#then
#system=`uname -n`
#fi
diskgroup="`echo $database | tr [A-Z] [a-z]`dg"
system=`hares -state ${diskgroup} | grep ONLINE | nawk '{print $3}'`
if [ -z "${system}" ]
then
echo "resource group ${database} has not been started on any system in the cluster. Please request administrator to start resourcegroup" | tee -a ${LOGFILE}
fi
status=`hares -state ${database}_ORA -sys ${system} | nawk '{print $NF}'`
if [ "${status}" = "FAULTED" ]
then
echo "`date +%Y%m%d-%H%M`: Clearing faulted resource ${database}_ORA on system ${system}" | tee -a ${LOGFILE}
hares -clear ${database}_ORA -sys ${system} 2>&1 | tee -a ${LOGFILE}
fi
echo "`date +%Y%m%d-%H%M`: Onlining resource ${database}_ORA on system ${system}" | tee -a ${LOGFILE}
hares -online ${database}_ORA -sys ${system} 2>&1 | tee -a ${LOGFILE}
sleep 5
temp=`hares -state ${database}_ORA | grep ONLINE | nawk '{print $3}'`
while [ -z "${temp}" ]
do
seconds=count*5
echo "`date +%Y%m%d-%H%M`: Database resource ${database}_ORA not yet online after ${seconds} seconds" | tee -a ${LOGFILE}
echo "`date +%Y%m%d-%H%M`: Status of resource ${database}_ORA" | tee -a ${LOGFILE}
hares -state ${database}_ORA 2>&1 | tee -a ${LOGFILE}
if [ $seconds -gt 300 ]
then
echo "`date +%Y%m%d-%H%M`: Database resource ${database}_ORA still not online after ${seconds} seconds. We are exiting the script. Please check the Oracle alert log" | tee -a ${LOGFILE}
exit 1
fi
sleep 5
temp=`hares -state ${database}_ORA | grep ONLINE | nawk '{print $3}'`
count=count+1
done
echo "`date +%Y%m%d-%H%M`: Database Resource ${database}_ORA is online on system ${system}" | tee -a ${LOGFILE}
system=`hares -value ${database}_LSNR LastOnline`
if [ "${status}" = "FAULTED" ]
then
echo "`date +%Y%m%d-%H%M`: Clearing faulted resource ${database}_LSNR on system ${system}" | tee -a ${LOGFILE}
hares -clear ${database}_LSNR -sys ${system}
fi
echo "`date +%Y%m%d-%H%M`: Onlining resource ${database}_LSNR on system ${system}" | tee -a ${LOGFILE}
hares -online ${database}_LSNR -sys ${system} 2>&1 | tee -a ${LOGFILE}
count=1
sleep 5
temp=`hares -state ${database}_LSNR | grep ONLINE | nawk '{print $3}'`
while [ -z "${temp}" ]
do
seconds=count*5
echo "`date +%Y%m%d-%H%M`: Listener resource ${database}_LSNR not yet online after ${seconds} seconds" | tee -a ${LOGFILE}
echo "`date +%Y%m%d-%H%M`: Status of resource ${database}_LSNR" | tee -a ${LOGFILE}
hares -state ${database}_LSNR 2>&1 | tee -a ${LOGFILE}
if [ $seconds -gt 300 ]
then
echo "`date +%Y%m%d-%H%M`: Listener resource ${database}_LSNR still not online after ${seconds} seconds. We are exiting the script. Please check the Listener alert log" | tee -a ${LOGFILE}
exit 1
fi
sleep 5
temp=`hares -state ${database}_LSNR | grep ONLINE | nawk '{print $3}'`
count=count+1
done
echo "`date +%Y%m%d-%H%M`: Listener Resource ${database}_LSNR is online on system ${system}" | tee -a ${LOGFILE}
resources=`hares -dep | grep "${database}_LSNR$" | nawk '{print $2}'`
for res in ${resources}
do
echo "`date +%Y%m%d-%H%M`: Onlining resource ${res} on system ${system}" | tee -a ${LOGFILE}
hares -online ${res} -sys ${system}
sleep 5
echo "`date +%Y%m%d-%H%M`: Status of resource ${res}" | tee -a ${LOGFILE}
hares -state ${res} 2>&1 | tee -a ${LOGFILE}
done
;;
stop)
typeset -i count
count=0
resources=`hares -dep | grep "${database}_LSNR$" | nawk '{print $2}'`
for res in ${resources}
do
echo "`date +%Y%m%d-%H%M`: Offlining resource ${res} on system ${system} which depends on resource ${database}_LSNR" | tee -a ${LOGFILE}
system=`hares -state ${res} | grep ONLINE | nawk '{print $3}'`
if [ -n "${system}" ]
then
hares -offline ${res} -sys ${system} 2>&1 | tee -a ${LOGFILE}
fi
sleep 5
temp=`hares -state ${res} | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk '{print $3}'`
count=1
while [ -n "${temp}" ]
do
seconds=count*5
echo "`date +%Y%m%d-%H%M`: resource ${res} not yet offline after ${seconds} seconds" | tee -a ${LOGFILE}
echo "`date +%Y%m%d-%H%M`: Status of resource ${res}" | tee -a ${LOGFILE}
hares -state ${res} 2>&1 | tee -a ${LOGFILE}
if [ $seconds -gt 300 ]
then
echo "`date +%Y%m%d-%H%M`: resource ${res} still not offline after ${seconds} seconds. We are exiting the script. Please check the alert log" | tee -a ${LOGFILE}
exit 1
fi
sleep 5
count=count+1
temp=`hares -state ${res} | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk '{print$3}'`
done
FAULTED=`hares -state ${res} | grep FAULTED | nawk '{print $3}'`
if [ -n "${FAULTED}" ]
then
hares -clear ${res} -sys ${system} 2>&1 | tee -a ${LOGFILE}
fi
echo "`date +%Y%m%d-%H%M`: Resource ${res} is offline on system ${system}" | tee -a ${LOGFILE}
done
system=`hares -state ${database}_LSNR | grep ONLINE | nawk '{print$3}'`
if [ -n "${system}" ]
then
echo "`date +%Y%m%d-%H%M`: Stopping ${database}_LSNR on ${system}" | tee -a ${LOGFILE}
hares -offline ${database}_LSNR -sys ${system} 2>&1 | tee -a ${LOGFILE}
fi
sleep 5
count=1
temp=`hares -state ${database}_LSNR | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk '{print$3}'`
while [ -n "${temp}" ]
do
seconds=count*5
echo "`date +%Y%m%d-%H%M`: Listener resource ${database}_LSNR not yet offline after ${seconds} seconds" | tee -a ${LOGFILE}
echo "`date +%Y%m%d-%H%M`: Status of resource ${database}_LSNR" | tee -a ${LOGFILE}
hares -state ${database}_LSNR 2>&1 | tee -a ${LOGFILE}
if [ $seconds -gt 300 ]
then
echo "`date +%Y%m%d-%H%M`: Listener resource ${database}_LSNR still not offline after ${seconds} seconds. We are exiting the script. Please check the listener alert log" | tee -a ${LOGFILE}
exit 1
fi
sleep 5
count=count+1
temp=`hares -state ${database}_LSNR | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk '{print$3}'`
done
echo "`date +%Y%m%d-%H%M`: Listener Resource ${database}_LSNR is offline on system ${system}" | tee -a ${LOGFILE}
system=`hares -state ${database}_ORA | grep ONLINE | nawk '{print$3}'`
if [ -n "${system}" ]
then
echo "`date +%Y%m%d-%H%M`: Stopping ${database}_ORA on ${system}" | tee -a ${LOGFILE}
hares -offline ${database}_ORA -sys ${system} 2>&1 | tee -a ${LOGFILE}
fi
sleep 5
count=1
temp=`hares -state ${database}_ORA | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk '{print$3}'`
while [ -n "${temp}" ]
do
seconds=count*5
echo "`date +%Y%m%d-%H%M`: Database resource ${database}_ORA not yet offline after ${seconds} seconds" | tee -a ${LOGFILE}
echo "`date +%Y%m%d-%H%M`: Status of resource ${database}_ORA" | tee -a ${LOGFILE}
hares -state ${database}_ORA 2>&1 | tee -a ${LOGFILE}
if [ $seconds -gt 300 ]
then
echo "`date +%Y%m%d-%H%M`: Database resource ${database}_ORA still not offline after ${seconds} seconds. We are exiting the script. Please check the database alert log" | tee -a ${LOGFILE}
exit 1
fi
sleep 5
count=count+1
temp=`hares -state ${database}_ORA | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk '{print$3}'`
done
echo "`date +%Y%m%d-%H%M`: Database Resource ${database}_ORA is offline on system ${system}" | tee -a ${LOGFILE}
;;
esac
Following a database crash...[edit]
Check the status of the datafiles and tablespaces. They might need recovering.
Restarted and checked alert log. datafile 6 was in RECOVER status
SQL> recover datafile 6; Checked dba_datafiles. File is now in OFFLINE status SQL> alter database datafile 6 online; ... Errors in file e:\oracle\product\admin\arisppmp\bdump\arisppmp_lgwr_5104.trc: ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 4956' Wed Apr 30 02:35:29 2014 System State dumped to trace file e:\oracle\product\admin\arisppmp\bdump\arisppmp_lgwr_5104.trc Wed Apr 30 02:35:30 2014 Errors in file e:\oracle\product\admin\arisppmp\udump\arisppmp_ora_13032.trc: ORA-00603: ORACLE server session terminated by fatal error ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: 'G:\ORADATA\ARISPPMP\ARISPPMP01.DBF' ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: 'G:\ORADATA\ARISPPMP\ARISPPMP01.DBF' Killing enqueue blocker (pid=4956) on resource CF-00000000-00000000 by killing session 167.1 Wed Apr 30 02:35:38 2014 Errors in file e:\oracle\product\admin\arisppmp\bdump\arisppmp_dbw0_4956.trc: Wed Apr 30 02:40:30 2014 Errors in file e:\oracle\product\admin\arisppmp\bdump\arisppmp_lgwr_5104.trc: ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 4956' Wed Apr 30 02:40:30 2014 System State dumped to trace file e:\oracle\product\admin\arisppmp\bdump\arisppmp_lgwr_5104.trc Killing enqueue blocker (pid=4956) on resource CF-00000000-00000000 by terminating the process LGWR: terminating instance due to error 2103 Wed Apr 30 02:40:32 2014 Errors in file e:\oracle\product\admin\arisppmp\bdump\arisppmp_arc1_6920.trc: ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) Wed Apr 30 02:40:32 2014 Errors in file e:\oracle\product\admin\arisppmp\bdump\arisppmp_arc0_6912.trc: ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) Wed Apr 30 02:40:33 2014 Errors in file e:\oracle\product\admin\arisppmp\bdump\arisppmp_arc0_6912.trc: ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) Wed Apr 30 02:40:33 2014 Master background archival failure: 2103 Wed Apr 30 02:40:33 2014 Errors in file e:\oracle\product\admin\arisppmp\bdump\arisppmp_psp0_4848.trc: ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) Wed Apr 30 02:40:33 2014 Errors in file e:\oracle\product\admin\arisppmp\bdump\arisppmp_dbw2_5056.trc: ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) Wed Apr 30 02:40:35 2014 Errors in file e:\oracle\product\admin\arisppmp\bdump\arisppmp_smon_5208.trc: ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) Wed Apr 30 02:40:36 2014 Instance terminated by LGWR, pid = 5104 ...
How big is the database?[edit]
Calculate the total database size
set lines 132 col dbname for a9 heading "Database" col dbsize for a15 heading "Size" col dbused for a15 heading "Used space" col dbfree for a15 heading "Free space" with used_space as ( select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log ) , free_space as ( select sum(bytes) sum_bytes from dba_free_space ) select vd.name dbname , round(sum(used_space.bytes)/1024/1024/1024 ) || ' GB' dbsize , round(sum(used_space.bytes)/1024/1024/1024 ) - round(free_space.sum_bytes/1024/1024/1024) || ' GB' dbused , round(free_space.sum_bytes/1024/1024/1024) || ' GB' dbfree from free_space , used_space , v$database vd group by vd.name , free_space.sum_bytes /
How big are the tablespaces?[edit]
Report the database size in terms of tablespace
set pages 100 lines 200
select df.tablespace_name "Tablespace"
, totalusedspace "Used MB"
, (df.totalspace - tu.totalusedspace) "Free MB"
, df.totalspace "Total MB"
, round( 100 * ( (df.totalspace - tu.totalusedspace) / df.totalspace) ) "Pct. Free"
from (
select tablespace_name
, round(sum(bytes) / 1048576) totalspace
from dba_data_files
group by tablespace_name
) df
, (
select round(sum(bytes)/(1024*1024)) totalusedspace
, tablespace_name
from dba_segments
group by tablespace_name
) tu
where df.tablespace_name = tu.tablespace_name
and df.totalspace != 0
order by 5
/
Database Free space report[edit]
From asktom
- Tablespace Name: name of tablespace, leading '*' indicates a good locally managed tablespace, leading blank means it is a bad dictionary managed tablespace. Second character of A implies ASSM managed storage, second character of M implies manually managed (pctused, freelists, etc are used to control space utilization)
- Mbytes: allocated space of the tablespace, sum of mbytes consumed by all datafiles associated with tablespace.
- Used: space in the tablespace that is used by some segment.
- Free: space in the tablespace not allocated to any segment.
- %Used: ratio of free to allocated space
- largest: mostly useful with dictionary managed tablespaces, the size of the largest contiguous set of blocks available. If this number in a dictionary managed tablespace is smaller than the next extent for some object, that object could fail with "out of space" even if the FREE column says there is lots of free space.
- MaxPoss Kbytes: the autoextend max size (note CAN be smaller than the allocated size!!!! you can set the maxsize to be less than the current size of a file)
- %Max Used: how much of the maximum autoextend size has been used so far
set linesize 132
-------------------------------------------------------
-- This SQL Plus script lists freespace by tablespace
--------------------------------------------------------
column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a19 heading "Tablespace Name"
column Mbytes format 999,999,999 heading "MBytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
column max_size format 9,999,999,999,999 heading "MaxPoss|Mbytes"
column pct_max_used format 999.9 heading "%|Max|Used"
break on report
compute sum of mbytes on report
compute sum of free on report
compute sum of used on report
select (select decode(extent_management,'LOCAL','*',' ') ||
decode(segment_space_management,'AUTO','a ','m ')
from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
mbytes_alloc mbytes,
mbytes_alloc-nvl(mbytes_free,0) used,
nvl(mbytes_free,0) free,
((mbytes_alloc-nvl(mbytes_free,0))/
mbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(mbytes_max,mbytes_alloc) Max_Size,
decode( mbytes_max, 0, 0, (mbytes_alloc/mbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024/1024 mbytes_free,
max(bytes)/1024/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024/1024 mbytes_alloc,
sum(maxbytes)/1024/1024 mbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024/1024 mbytes_alloc,
sum(maxbytes)/1024/1024 mbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 8
/
Which schemas are taking up all of the space?[edit]
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc
, 2 desc
, 1;
Show the ten largest objects in the database[edit]
colowner format a15
colsegment_name format a30
colsegment_type format a15
colmb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where 1=1
and rownum < 11;
Is java installed in the database?[edit]
This will return 9000'ish if it is
select count(*) from all_objects where 1=1 and object_type like '%JAVA%' and owner = 'SYS';
Show character set information[edit]
select * from nls_database_parameters;
Show all used features[edit]
select name , detected_usages from dba_feature_usage_statistics where 1=1 and detected_usages > 0;
Move a database from one host to another without RMAN[edit]
Once upon a time, this was the only way to move a database to a different server. Now it's not common as RMAN can do it so well.
On source server
Get a text version of the controlfile[edit]
alter database backup controlfile to trace as '/tmp/&database_name._cf.sql';
Grab a copy of the pfile[edit]
create pfile='/tmp/init_&database..ora' from spfile;
Send these 2 files to the destination server[edit]
scp /tmp/init*ora /tmp/*_cf.sql oracle@${dest_host}:/tmp/
Shutdown the database[edit]
shu immediate
Copy the database datafiles to the new host (see in the controlfile creation script where they are)[edit]
As we are going to reopen the database with resetlogs, no need to copy the redo logs.
As a new did will be created, no need to copy the archivelogs. If we need the old database back, we can just restart it on this server.
scp /cln/exp/ora_data3/cocpit/*dbf oracle@hn512:/cln/exp/ora_data2/cocpit/
On destination server
Edit the files[edit]
- Change path names for new filesystem(s)
- Create any directories that don't exist (archivelog, audit, ...)
- Modify the controlfile recreation script
- Remove Part #1 (use the resetlogs option)
- Change REUSE to SET
- Remove the recover database and open statements
- Comment out the alter tablespace temp statements. Might need it after opening.
Add database entry to /etc/oratab[edit]
Create or add entry to listener.ora[edit]
Create or add entry to tnsnames.ora[edit]
Setup environment for database[edit]
. .oraenv
Copy init file to where it can be used[edit]
cp /tmp/init${ORACLE_SID}.ora ${ORACLE_HOME}/dbs/
Create the new controlfile[edit]
cd /tmp sqlplus / as sysdba @&database_name._cf
Mount the database[edit]
alter database mount
Open the database[edit]
alter database open resetlogs
Check the database[edit]
set lines 2000 pages 100 col file_name for a80 select file_name, status, online_status from dba_data_files; select file_name, status, online_status from dba_temp_files;
