Jephe Wu - http://linuxtechres.blogspot.com
Objective: to take over a new database and issue some commands to know the basic things about this database
Commands:
a. know what platform it is (workable on 10g)
select name, platform_id,platform_name from v$database;
note: use the following view result to get the endian table:
select PLATFORM_ID,platform_name,endian_format from v$transportable_platform;
b. check if it's cluster database (RAC)
show parameter cluster_database;
c. check instance status
select * from v$instance;
note: you can find out instance_name, version, startup_time, status etc
d. check database status
select * from v$database;
select open_mode from v$database;
note: you can find out dbid, dbname, db_uniq_name, created_date, log_mode, db_role, platform_name etc
e. check datafile, logfile and controlfile name and location
select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;
select destination from v$archive_dest;
select name from v$tempfile; temp file name
f. check archive log status
archive log list;
or
select log_mode from v$database;
g. check datafile, tempfile and online logfile size
select sum(bytes)/1024/1024 "MB" from dba_data_files; Size of datafiles in MB
select sum(bytes)/1024/1024 "MB" from dba_temp_files; Size of tempfiles in MB
select sum(bytes)/1024/1024 from v$log; Size of redo logs in MB (Not accounting for mirrored redolog files)
select sum(bytes)/(1024*1024) from dba_segments;
select a.data_size+b.temp_size+c.redo_size+d.cont_size "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$logfile lf, sys.v_$log l
where lf.group# = l.group#) c,
( select sum(block_size*file_size_blks) cont_size
from v$controlfile ) d;h. check using spfile or pfile
show parameter spfile;
i. check dataguard status
select database_role from v$database;
(primary or standby)
dgmgrl / as sysdba
show configuration
# check archive log destination settings.
select destination, error from v$archive_dest;
j. check characterset
select * from nls_database_parameters;
select * from v$nls_parameters;
commonly used commands:
1. alter system register
2. alter system checkpoint
3. alter system switch logfile;
References:
1. alter system syntax
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2013.htm#SQLRF00902
Posts
