Managing Database & Instance Structure

Hello everyone,

In this article I will summarize some of the commands used during managing database & Instance.

-- Some Parameters used during this article

> show parameter db_name
> show parameter instance_name
> show parameter db_unique_name

> show parameter db_block_size            -- find default blog size of database, cannot change

> show parameter SPFILE
> show parameter pfile

> show parameter background_dump_dest        -- alert log file location
> show parameter control_file                            -- name and location of control files


-- Selects and commands used during this chapter

-- To know about datafiles in your DB & size of each (in MB) (DBA_DATA_FILES)
-- Also used to view Undo Tablespaces's datafile(s)
> select file_name, bytes/1024/1024 "MB"
from dba_data_files;

-- To see when data file created (V$DATAFILE)
> select name, creation_time
from v$datafile;

-- To know about control files names and locations
> select name from v$controlfile;

-- To know about your redo log files (v$logfile)
> select member, status, group#
from v$logfile;

-- To know about your redo log groups (v$log)
> select group#, sequence#, bytes/1024/1024 "MB", archived, status
from v$log;
-----------------------------------------------------------------------------

-- To add a member to each of redo groups

-- No need to specify size, he add with group size specified when creating group
-- Use v$logfile query to get log files names & location
> select member, status, group#
from v$logfile;

> ALTER DATABASE
ADD LOGFILE MEMBER '/full/path/to/log/file/logfile_name.log'
to group group_number;

-- redo log file name and location with group_number based on what are u doing.
-- repeat for each group
-- check the result using v$logfile select statement
-- note tht the status of new logfiles is invalid

-- To change the status of new added files to be available to db
-- Repeat as number of redo log groups to make them available to be used
> alter system switch logfile;
---------------------------------------------------------------------------------


-- To add a completely new redo log group
-- You have to specify size for the new group

-- 1. To know your redo log groups (to get size)
> SELECT group#, sequence#, bytes/1024/1024 "MB", archived, status
from v$log;

-- 2. To know your redo log files (to get files Location)
> SELECt member, status, group#
from v$logfile;

> ALTER DATABASE
ADD LOGFILE
('/full/path/to/log/file/logfile_name.log','/full/path/to/log/file/logfile_name.log')
size 50m;        -- 50 mega for example
-- Use the two previous two selects to check new group & its members
---------------------------------------------------------------------------------


-- To drop a redo log group

-- Use the two selects to know your groups and members
> ALTER DATABASE
drop logfile group group_number;
---------------------------------------------------------------------------------


-- Notes:
Note 1: Regarding port number used to connect
you logged on to server with database and listener started
to know the listening information to connect to this database
use the "lsnrctl status" command
will display hostname & port used for listener

Note 2: regarding control files
in 11g the defualt is 2 files only not 3 as 10g
& also one of them located with the datafies and the other
one is in the flash recovery area.
in 10g they are 3 and all under datafile directory

Note 3: regarding control files
if media failure for one of them oracle will tell you
copy from one place to the other and fix media

Best Regards
Mostafa