Database Maintenance: 2013

create database sss
logfile group 1 ('/sss/redo01.log') size 100m,
group 2 ('/sss/redo02.log') size 100m
datafile '/sss/system01.dbf' size 325m
sysaux datafile '/sss/sysaux01.dbf' size 400m
default tablespace users datafile '/sss/users01.dbf' size 400m
default temporary tablespace tempts1
tempfile '/sss/temp01.dbf' size 10m
undo tablespace undotbs datafile '/sss/undotbs01.dbf' size 50m;

Managing Controlfile.

Controlfile is a binary file.

It contains

database name
locations of logfile and datafiles
checkpoint information
db creation time
current LSN

demo :

ALTER DATABASE BACKUP CONTROLFILE TO '/gopi/ctl.bkp';

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Mulitplexing or adding

Droping controlfile

renaming controlfile

Related Views
v$database
v$controlfile
v$controlfile_record_section
v$parameter

Logfile

set linesize 130

col member format a30

col group# format 99

col thread# format 99

select group#,thread#,bytes,members,archived,status from v$log;

select group#,status,type,member from v$logfile

select l.group#,l.thread#,l.bytes,l.members,l.archived,l.status,lg.status,lg.type,lg.member from v$logfile lg, v$log l where l.group#=lg.group#;

ALTER DATABASE ADD LOGFILE GROUP 3 ('redo04a.log','redo04b.log')   SIZE 100M;
ALTER DATABASE ADD LOGFILE ('log1c.rdo', 'log2c.rdo') SIZE 100M;
ALTER DATABASE ADD LOGFILE MEMBER 'log2b.rdo' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo' TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');
ALTER DATABASE RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo'
           TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';

           SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

           ALTER DATABASE DROP LOGFILE GROUP 3;

           ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo';


           ALTER SYSTEM SWITCH LOGFILE;

           ALTER DATABASE CLEAR LOGFILE GROUP 3;

           ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

           Views

           V$LOG
           V$LOGFILE
           V$LOG_HISTORY

ARCHIVE LOG

Changing to archive log from no archive log;

ALTER DATABASE ARCHIVELOG MANUAL;

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=6; def : 4 max : 30

     LOG_ARCHIVE_DEST_n

    LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc

ARCHIVE LOG LIST;

    V$DATABASE
V$ARCHIVED_LOG
V$ARCHIVE_DEST
V$ARCHIVE_PROCESSES
V$BACKUP_REDOLOG
V$LOG
V$LOG_HISTORY

What are the information i need to get for database maintenance from client?

No of servers and how many databases in each server?
How many production/test/development environments?
What kind of application and how many tiers ?
- please specify the softwares that were installed.
Is there any High Availability concepts implemented?
What is the backup strategy currently in place?
What is the size of databases?
Average number of issues per week?
p1 -
p2-
p3-
p4-

Do you have any ticketing systems
What type of application?
tier 2,tier 3,tier n
application software?
Domain information
Do you have RAC database /Dataguard
database size
Can we have lease line
What is the backup strategy and backup duration?
Do you take backup for test environments
What is the storage type?

servers?
growth of the database.

OS version
Database version
webserver
application server

Database Maintenance

Friday, 2 August 2013

Architecture

Saturday, 8 June 2013

Database Maintenance